Forum Discussion
SUMIF function error
I have a table where Column 7 contains the "Due Date" of the value in Column 9.
Some of the "Due Dates are in the future (outside the reporting date) so I created the following formula:
=SUMIF((R5C9:R15C9),(R5C7:R15C7<=Max_Due_Date),R18C12)
to obtain the total amount within the reporting period -- i.e. less than or equal to the last date in the reporting period (a named item containing the calculated date of the first of the current month, plus the number of days in the month, minus 1), and want the result in Row 18 Column 12.
I get the error code "SPILL#", with a "hatched lines" box immediately below the formula cell. I am not trying to get a Pivot Table, as suggested by the unhelpful "Help"! I have used "SUMIF" many times in the past without problems.
H E L P , please
5 Replies
Also, thank you for your VERY prompt suggestion -- thank you.
The formula SHOULD have been;
=SUMIF((R5C7:R15C7),"<Max_Due_Date",(R5C9:R15C9))which yields "0", where the correct value is "821.43" from the following table:
3 Nov 2025 14 Nov 2025 620.40 25 Nov 2025 21 Nov 2025 21.00 27 Nov 2025 1 Dec 2025 961.84 8 Nov 2025 0.00 21 Mar 2026 575.00 13 Sep 2026 13 Nov 2025 123.98 5 Nov 2025 56.05 3 Nov 2025 30 Nov 2025 Actually that intermediate " and & are very important. If you leave the Max_Due_Date inside the quotes excel will not evaluate it as a named range but as text and hence result in 0. Try moving it outside the quotes. See the image below showing both inside and outside the quotes:
and I forgot to mention previously, I believe your named calculation:
"a named item containing the calculated date of the first of the current month, plus the number of days in the month, minus 1"could be achieved easier using
=EOMONTH(TODAY(),0)
So a few things:
"SPILL#" error is because the formula you wrote is returning more than 1 value (e.g. an array of answers) but those additional results would overlap one or more cells on the sheet that you already have something entered (even if that something is a space/blank)
SUMIF format uses a range to CHECK, the CONDITION that should be used to do the check, and then an optional range to do the sum on (otherwise it will some the range being checked). Your formula is giving the range to sum as the check range, the conditional is incorrectly formatted, and for the sum range you have where you want the output, which is also not going to work.
Here is the formula you want:
=SUMIF(R5C7:R15C7,"<="&Max_Due_Date,R5C9:R15C9)So if you want a breakdown of what your formula is doing:
the conditional you have will return an array of results each being true or false. This array of true and false are then, one by one, being tested against the whole CHECK range and then the sum of the cells starting at R18C12 are being summed when a match is found. SO, if the first date comparison is TRUE then is looks for all the TRUE values in C9 and then will sum all the corresponding values in C12 (but shifted down to R18). Then it will repeat with the 2nd, 3rd, etc.. result of the conditional results. That is why you have a SPILL error because it is trying to return a range of results for all of those conditional results. Hope that helps.
I had already corrected the formula, but without your intermediate " and &. Both my version and yours return "0"
The only difference between our tow versions are the inclusion of unnecessary but innocuous internal "()" pairs around the ranges. Any suggestions? (In the table in my previous correction, the final "20 Nov 2025" is the named item "Max_Due_Date", and Column 8 was hidden.)
