SUMPRODUCT Error by Month

Occasional Contributor

The last couple of days I've been attempting to count the number of instances in a given month listed on a table. 

 

The equation that I'm using is SUMPRODUCT(--((Month(Tracker[Date Closed])=1))).

 

The good news this is working for every month...EXCEPT January. In testing, even when I deleted all of the data there is still a total of 1 showing up for January, although ironically, when I add data back in excluding January all of the other months show up correctly and January reverts to the correct number 0. If anything, when data is deleted it seems like the January count is counting the number of existing rows on the table even though they're blank.

 

The photos below show the table that I'm attempting to pull data from (the golden one which is currently empty). The purple chart is the one I'm attempting to show the months on. For added reference in my equation, the table is named "Tracker" and the column I'm pulling from is named "Date Closed".

 

busymamabee_0-1668285122984.png

busymamabee_1-1668285146485.png

 

1 Reply

@busymamabee 

The MONTH function treats empty cells as 0. 0 corresponds to the (non-existent) date January 0, 1900, so MONTH(empty_cell) = 1.

You could use

 

=SUMPRODUCT((Tracker[Date Closed]<>"")*(Month(Tracker[Date Closed])=1))