SUM entries MTD for each day and each month
I have a sheet with the date on each row starting 07/01/2021 through the end of the fiscal year, 06/30/2022. I need the month to date sum on each row. I sum each date with the date before but I have to manually change the formula at the end of each month. Is there a way to do this automatically so I don't have to restart the formula at the beginning of each month? I feel like it should be a simple fix.
(Here is the formula in the 911 Calls MTD column for each date)
07/01/2021 =SUM([911 Calls Daily]1)
07/02/2021 =SUM([911 Calls MTD]1 + [911 Calls Daily]2)
07/03/2021 =SUM([911 Calls MTD]2 + [911 Calls Daily]3)
Best Answer
-
I spent the past two days trying to figure it out and I finally got it. I added a helper column to determine the Month and used the following formula:
=SUMIFS([911 Calls Daily]$1:[911 Calls Daily]@row, Month$1:Month@row, Month1)
This formula gives me a running MTD total and automatically resets itself on the first of the month.
Answers
-
I spent the past two days trying to figure it out and I finally got it. I added a helper column to determine the Month and used the following formula:
=SUMIFS([911 Calls Daily]$1:[911 Calls Daily]@row, Month$1:Month@row, Month1)
This formula gives me a running MTD total and automatically resets itself on the first of the month.
Help Article Resources
Categories
Check out the Formula Handbook template!