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
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
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])
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
