Excel: Dynamic Summing To-Date

Here is our example spreadsheet:

xscreen1.png

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:

xscreen2.png

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:

xscreen3.png

Conditional formatting to highlight rows is based on the formula:

=(ROW()-ROW(I$2)=$H$24)

xscreen4.png

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

 
1
Kudos
 
1
Kudos

Now read this

Excel – Using the CELL function

Here’s a small selection of useful functions, including CELL, MID and FIND. Typing “=CELL(“ in Excel will drop this menu down. Most are self-explanatory, but there are some uses for CELL with filename that are “different”. For example,... Continue →