Forum Discussion
How to get excel to display values conditional to current Month
Hello
I have a personal billing excel sheet to keep track of monthly charges
Some charges are every 3 months, some are every 4 months, so the monthly expectation is different each month
I want to find a way to have a cell display the expected charges based on the month
I figure i can use current Month reference using =MONTH(TODAY()) but dont know how to create a condition to have the MONTHLY $ to display the value for the Month (how to write a formula which will display the Dec value of 63.63 in the Monthly $ when the Month calculates to 12)
| 11 | ||
| AUDIBLE | 17.00 | |
| BULLYMAKE | 46.63 | |
| Feb | 17.00 | |
| Mar | 46.63 | |
| Apr | 17.00 | |
| June | 63.63 | |
| Aug | 17.00 | |
| Sept | 46.63 | |
| Oct | 17.00 | |
| Dec | 63.63 | |
| monthly $ |
Thanks for any help
2 Replies
Alternatively, if you enter your monthly Dates with Day = 1 (i.e. 2024-01-01, 2024-02-01...) every month you can use the following option:
=SUMIF( MyTable[Date], EOMONTH( TODAY(), -1 ) +1, MyTable[Amount] )See sample attached file. I did it with a Table as I only use table. You can convert it to a Range no problem.
The Key point is your Date column must contain actual Date values i.e. 1/1/24 and not Text values like "Jan". Once you have entered your dates you can format them to display as Jan, Feb...
And next times, please don't forget to mention the version of Excel you run - Thanks
