Forum Discussion
SUMPRODUCT Error by Month
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)))
I want to count how many times January (month=1) is repeated between Start (31-Jan-22) and End Date (31-Aug-25). Is there a tweak for this formula to get that result?
