Sumif/Sumproduct formula to add all values up until selected month

[Edmond_Dantes]

Hi guys

Hope someone can help.

I need a formula that will sum all the expenditure related to a particular item up until the end of a month which is selected by the user.

The user will select the month from a dropdown list on a separate worksheet from the data. Say this dropdown lies in B3.

This is what I've got so far:
=SUMIF(Data!M:M,$B15, Data!Y:Y)
=SUMIF(Data!M:M,$B15, Data!Z:Z)
........
=SUMIF(Data!M:M,$B15, Data!AJ:AJ)

Column M in the datasheet contains a list of items, say books, dvds etc.
Column Y in the datasheet contains expenditure for April 2011, Z for May 2011 and so on
The matching item lies in B15, say "books."

I would need a single formula that will calculate all the expenditure for the item "books" up until the end of the month which the user selects.

I hope this is all clear. Any help would be appreciated.

 

[anglais428]

You could maybe tidy this up a bit (if possible). I would have all the "matching items" in one column (my column A), all the dates in column B, a new column C which would just have the month name (formula is =Text(b1,"MMMM") and pull down), Column D expenditure.

Then have your selection in other cell e.g. Select month in B5 and Select matching item in B6

Then =SUMIFS(D:D,A:A,B6,C:C,B5)

 

[Edmond_Dantes]

Thank you Anglais!

I will try that now