SUMPRODUCT not working as expected - #VALUE error

Question

Hi

I have a column of sequential dates (A) and a column with a value (D). I need a total by calendar month. I used the following formula to sum totals by year and month.

=SUMPRODUCT((MONTH($A$4:A15)=MONTH(A15))*(YEAR($A$4:A15)=YEAR(A15))*($D$4:D15))

The range starts at A4 and is limited to end at the current row, to save the formula having to needlessly check future dates.

This returns a #VALUE error. Even if I remove the MONTH and YEAR functions and replace them with a fixed value I still get the error. This happens on both Windows and MacOS.

Where is my error? I just can't see it.

Answer 1

Ah, I found the problem. It is not the dates.

The Days column is a calculation. If a source cell is empty it returns "". I changed it to return 0 and the SUMPRODUCT now works. Thanks for the pointer.

Answer 2

Changing the cell formatting is not sufficient if you have text in the cells.

Convert numbers stored as text to numbers - Office Support

If you need further help we need to see your file.

Andreas.

Answer 3

According to the spreadsheet, the dates are formatted as Date, both in the Format Cells dialogue and in the format box on the Ribbon, so I would expect the formula to work.

Answer 4

The issue is in your data, either your dates or values are text, not numbers. The formula works as expected.

E4: =SUMPRODUCT((MONTH($A$4:A4)=MONTH(A4))*(YEAR($A$4:A4)=YEAR(A4))*($D$4:D4))

dragged down.

Andreas.