My challenge this week was to show the running total in a list, but only for the current date and earlier. I wanted rows for future dates to appear empty, but have the formulas in them – ready to go. How would you solve that problem?
Keeping Track of Activities
The project that I was working on was a simple list, to keep track of my daily walks.
My daughter works for the Movember Foundation, and she forced, errr, encouraged me to sign up. This year, you can MOVE every day, to support the cause, instead of growing a Moustache. So, I have an activity page on the Movember site, but I made my own tracking sheet, in Excel of course!
I filled in all the November dates in the first column, and each day I’ll enter the number of minutes that I walked.
Add Running Totals
Then, to see my progress, I added formulas for running totals, in columns D and E.
- In column D, a SUM formula gives a running total, by summing the numbers from C4, down to the current row:
- =SUM($C$4:$C4)
- In column E, a COUNT formula gives a running count, by counting the numbers from C4, down to the current row:
- =COUNT($C$4:$C4)
That worked well, but I didn’t like all the numbers showing up in the upcoming date rows. How could they be hidden?
As always with Excel, there are may ways to do what you need, but here’s how I solved the problem.
No Entry, No Results
Sometimes, I add an IF function in a formula, to hide the result until a specific column is filled in.
In this sheet, the dates are all filled in, so I couldn’t use that column as a test. Instead, I’ll check for an entry in column B (Activity).
=IF([@Activity]=””,””,COUNT($C$4:$C4))
That worked, but it didn’t just hide the results for upcoming days – it also hid the result if I skipped a day for some reason. (This is just a test – I did walk on Tuesday!)
Maybe I’m too picky, but I’d prefer to see the running total in that row too.
Check the Date
So, what I really want to do is show the running total for every day, up to the current date.
To calculate the current date, we can use the TODAY function. To check if the entry in column A (Date) is after today:
=[@Date]>TODAY()
I’ll add that test to the running total formulas:
=IF([@Date]>TODAY(),””,SUM($C$4:$C4))
=IF([@Date]>TODAY(),””,COUNT($C$4:$C4))
Now, if the date in column A is after today’s date, the result will be an empty string, so the cell will appear empty.
- Running totals will appear in all rows for today or earlier, even if there was no activity.
- No running totals in rows with future dates (Nov. 6th and later)
Download the Sample File
You could adapt this technique for other types of lists – it’s not just for Movember MOVE tracking!
You can download a copy from the Excel Sample Files page on my website — in the Functions section, look for FN0042 – MOVEMBER Activity Tracker Chart.
And if you want to support the cause, click this link to go to my Movember page.
_____________
