How to efficiently summarize data by category and month
Question: How to sum data by month and category (location) ?
Answer:
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.
Related posts:
Excel: How to automatically summarize preceding month and year
Excel: Efficiently navigate cells and do fast selections
Extract distinct unique sorted year and month list from a date series in excel
Recommended categories
