Search code examples
excelsumifssumproduct

How to use SUMPRODUCT using months and different criterion using Excel

I have a table that looks like the following:

Name   Date       Description
HallA  8/24/19    texttexttext 
HallB  8/29/19    texttexttext 
HallC  9/1/19     texttexttext 
HallB  9/4/19     texttexttext 
HallB  9/24/19    texttexttext 
HallC  10/1/19    texttexttext

I would like to count how many times each Hall appears within each month.

This information should populate a table that looks like this. 

Halls  August   September  October    .......   
HallA      1            0        0 
HallB      1            2        0
HallC      0            1        1

Additionally, it should be built to take more information.

I have been combining a solution to this from a number of resources, both of which are not correct.

=IF(SUMPRODUCT(--(MONTH($B$2:$B)=8)) - SUMPRODUCT(--($A:$A = "HallB")) - SUMPRODUCT(--($A:$A = "HallC")) > 0, SUMPRODUCT(--(MONTH($B2:$B)=8)) - SUMPRODUCT(--($A:$A = "HallB")) - SUMPRODUCT(--($A:$A = "HallC")), 0)

=SUMPRODUCT(((MONTH($B$2:$B)=8)))*($A:$A = "HallA")

I think I've been combining so many things to the point that I'm over complicating it...

Solution

  • Sometimes pivot table is a powerful tool in transforming data and here is one example:

    Pivot Table

    Please make sure the dates are in Date format but not Text format otherwise pivot table will not be able to group the dates.

    Cheers :)