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))
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?
=SUMPRODUCT((MONTH(range)=1)*(range>=DATE(2022, 1, 31))*(range<=DATE(2025, 8, 31))
Since the latest date to be included is 31-Jan-2025, you might change the last date in the formula to DATE(2025, 1, 31).
Thank you so much.
As variant
=SUM( --( MONTH( SEQUENCE( EndDate-StartDate + 1, , StartDate ) )=1 ) )Thank you so much.
- Thank you so much.
