SumProduct Returns Zero

[Guest]

I have entered the following formula on a summary sheet:

SUMPRODUCT(('issue detail'!MONTH(A$3:A$275)=7)*('issue detail'!B$3:B$275))

Issue Detail is the second sheet in the workbook. Column A contains dates
(every day of the year). Columns B thru E contain numbers. I am trying to
get a total for the month of July (and for all other months) for each column.
This formula keeps retuning zero. Why?

If I can get the formula to work, I would also break down the totals by
week. By the end of the year, with 12 months and 52 weeks, would another
formula be faster?

I am using Excel 2003.

Thanks, Ron

 

[Guest]

Try this:

SUMPRODUCT((MONTH('issue detail'!A$3:A$275)=7)*('issue detail'!B$3:B$275))

 

[Guest]

SUMPRODUCT may need to be entered as an array formula.

Enter the formula you quote below and then, instead of hitting ENTER, hit
CTRL+SHIFT+ENTER. Does it calculate correctly?

 

[Guest]

Also, as to the question of another formula being faster, it doesn't sound
like you're running the formula against a huge amount of data, and so given
today's processor speeds and copious amounts of RAM I don't know that you'd
notice an appreciable difference between the speed of this formula and
another one.

 

[Guest]

Thanks. That worked. Appreciate the help.

Ron

Try this:

SUMPRODUCT((MONTH('issue detail'!A$3:A$275)=7)*('issue detail'!B$3:B$275))