Article updated on April 08, 2008

Question: How to sum data by month and category (location) ?


In cell E2 the formula is =SUMPRODUCT(($A$2:$A$25>=DATE(2008,1,1))* ($A$2:$A$25<=DATE(2008,1,31))*($B$2:$B$25=E$1)). Let us see what this formula does.

#1 $A$2:$A$25>=DATE(2008,1,1) counts all dates that are younger than 1st January 2008.

#2 ($A$2:$A$25<=DATE(2008,1,31) counts all dates that are older than 31st January 2008.

#3 ($B$2:$B$25=E$1) counts all values that are equal to category "New York".

Iterating through every row
Lets start. First row:

Date Location
2008-04-18 London

Inside formula:
2008-12-13>=2008-01-01 : TRUE (See #1 above)
2008-12-13<=2008-01-31 : FALSE (See #2 above)
LONDON=NEW YORK : FALSE (See #3 above)

SUMPRODUCT(1*0*0) = 0

Then the formula iterates through the remaining rows and counts those that meet the conditions. Not a single row meets all the conditions in cell E2.