Forum Discussion
Solved
SUMPRODUCT Error by Month
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".
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))
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))
HansVogelaar, I appreciated the response to the issue with January. I would never have guessed blank cells are treated as January 0, 1900.
I have a similar situation. I'm trying to count the months using Sumproduct and tried your fix of <>", but I am still getting the same error. Obviously, I'm doing something wrong. My formula is =SUMPRODUCT((MONTH($J$2:$J$424)<>"")*(MONTH($J$2:$J$424)=MONTH(I439))). In I439, I have the month formatted as a date. All other months work, but January counts all the blanks. Do you have a fix?
If a cell is blank, its MONTH will return 1, so that month is not empty. Try
=SUMPRODUCT(($J$2:$J$424<>"")*(MONTH($J$2:$J$424)=MONTH(I439)))
