Forum Discussion
SUMIF function error
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)
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)
Thank you so much for your very helpful reply and for the equally helpful suggestion re EOMONTH; I have been using Excel since version 1.04 on the Mac (about 1988 or 1989 I think! and it was on a Mac with an external 10MB disk attached to a LaserWriter I) and I never bothered to experiment with "new" functions as they became available -- unless I needed them for a project and they were going to be used frequently (I'm a bit lazy that way -- "Don't change a winning team"!)
I is some time (try several years!) since I needed to use concatenation ("&") -- thank you for reminding me. I have (Literally) just made the decision to set aside some time to do a refresher in Excel,
Again, thank you so much.