sum if with parameters 1

[stoke]

hi,

i have a budget and want to show year to date figure accoring to an input cell.

i can extract data for that week with:
=LOOKUP($B$1,'[xyz.xls]sheet'!J1:BI1,'[xyz.xls]sheet'!J29:BI29)

B1 being input for desired week number
J1:BI1 being the week numbers 1 to 52
and J29:BI29 being the data range to extract from.

what i need to do is adapt this formula to show the sum of data from week 1 through to week selected in B1.

i would appreciate any help!
Thanks in advance,

 

[TonyJollans]

Hi stoke,

Another one that can be done with SUMPRODUCT ..

[blue][tt]=SUMPRODUCT(('[xyz.xls]sheet'!J1:BI1=$B$1)*('[xyz.xls]sheet'!J29:BI29))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[KenWright]

>>Another one that can be done with SUMPRODUCT ..

At this rate it will be easier to list the ones that can't

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Probably true Ken; it's pretty versatile

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[stoke]

Thanks Tony,

although this only gives me the data for the week referenced in B1......

i need it to sum all data from week one through to week in referenced in B1.

i can't see why this is happening with the product formula you gave me.......

 

[TonyJollans]

Hi Stoke,

Oops

That should have read ..

[blue][tt]=SUMPRODUCT(('[xyz.xls]sheet'!J1:BI1[highlight]<=[/highlight]$B$1)*('[xyz.xls]sheet'!J29:BI29))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[stoke]

Splendid, many many thanks.

as well as getting me out of a hole you've taught a valuable tool.

Cheers,