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

1. OFFSET’s 4th parameter is Height.
2. Pass it current month against named range “Totals2009”, i.e. cells B3:B14 (or Named Formula ThisMonth)
3. 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