Excel: Dynamic Summing To-Date
Here is our example spreadsheet:
The SUM formula in cell B15 is shown. It uses MONTH(NOW()), but could just as easily use a Named Formula:
=SUM(OFFSET(Totals2009,0,0,ThisMonth,1))
Here’s the formula in Name Manager:
How it works
- OFFSET’s 4th parameter is Height.
- Pass it current month against named range “Totals2009”, i.e. cells B3:B14 (or Named Formula ThisMonth)
- Here, it sums 10 rows against MONTH(NOW()), (October)
This MTD example, on 26th of any month, compares September & October:
Conditional formatting to highlight rows is based on the formula:
=(ROW()-ROW(I$2)=$H$24)
Where $H$24 uses COUNTIF in place of OFFSET:
=COUNTIF(OctDates,”<=”&(DATE(YEAR(H3),MONTH(H3),DAY(NOW()))))
OctDates is a range of all the days in October colums , formatted to show “d” (day) only, starting at row H3. September range is treated similarly, but green background cells are Saturdays and Sundays. These are
(conditionally formatted against WEEKDAY):
=WEEKDAY(F3,2)>5
That’s it! No more fiddling with ranges every week/month/year!
Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS can assist you with your Excel: enquiries@krs.co.dev