Log In
Register
Home
New Member
December 14, 2023
Offline
I have a strange issue.
The following formula works fine: =SUMIF(BudgetV9tbl[Mt '#],"="&TEXT(TODAY(),"m"),BudgetV9tbl[Budget])
It returns the sum of all the 'Budget' amounts in December (today's month expressed as a number)
But when i change the "=" for a "<=" or "<" it returns 0!!
I don't understand the problem.
Does anybody else have this problem? What is wrong??
Thanks
See Answer
Trusted Members
October 17, 2018
Offline
Hi, which version of Excel?
Keep in mind that the TEXT fucntion does not return a numeric value but a text with the number value
Answers Post
Trusted Members
Moderators
November 1, 2018
Offline
Further to what Hans said, using = will cause (for reasons only MS understand) SUMIF to convert numeric text to numbers before doing the comparison, but adding in > or < will not. That suggests that the values in your [Mt '#] column are stored as text, so you might use something like:
=SUMPRODUCT((N(BudgetV9tbl[Mt '#])<=MONTH(TODAY()))*BudgetV9tbl[Budget])
New Member
December 14, 2023
Offline
Thanks you Hans and Velouria.
Both your answers were useful. The problem was with the way I generated the month number in 'Mt #'. I used "=TEXT(A4,"M")" which generated text. Using "=MONTH(A4)" instead solved the problem. My original formula now works fine!
Thanks
1 Guest(s)
