David Hager commentedLooks like an easy pivot table solution to me.

Thanks for your comment! Now i know how to automatically refresh a pivot table when sheet is activated.

Excel 2003 and earlier versions: Create a dynamic named range

This also works in Excel 2007/2010 but it is easier to create a table.

  1. Click "Name Manager"
  2. Create a new named range, named Rng.
  3. Type in "Refers to:" window:
=Data!$A$1:INDEX(Data!$1:$65535, COUNTA(Data!$A:$A), COUNTA(Data!$1:$1))

Click Close button!

Excel 2007/2010: Create a dynamic table

  1. Select data range
  2. Click "Insert" tab on the ribbon
  3. Click table
  4. Click "My table has headers"
  5. Click ok

Adding new records to the table is easy. The table expands automatically.

Create pivot table

  1. Select table
  2. Click "Insert" tab on the ribbon
  3. Click "Pivot table"
  4. Excel 2007/2010: Type table name in "Table/Range" field
    Excel 2003 and earlier versions: Type dynamic named range Rng in "Table/Range" field
  5. Click OK

Setup pivot table

Remember, the original question was how to calculate a running total within a date range and a "client" criterion.

  1. Click and drag Date to "Row Labels" window
  2. Click and drag Client "Report Filter" window
  3. Click and drag Amount to "Values" window

How to auto-refresh pivot table

VBA offers a solution how to automatically refresh pivot table every time you activate "pivot table" sheet.

  1. Right click on the sheet name where you placed the pivot table
  2. Click "View code"
  3. Paste code into code window
  4. Change sheet name and pivot table name in vba code

VBA code:

Private Sub Worksheet_Activate()
    Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub

How to select a client

  1. Select "Pivot table" sheet
  2. Click "black arrow" on cell B1
  3. Select a client or multiple clients
  4. Click Ok

See picture below.

Excel 2007/2010: How to select a date range

  1. Select "Pivot table" sheet
  2. Click black arrow on cell B3
  3. Select "Date Filters"
  4. Select a date or dates, a month or months.

See picture below.

Download excel example file

Running totals within date range, pivot table.xlsm
(Excel 2007 Macro-Enabled Workbook *.xlsm)

Running totals within date range, pivot table.xls
(Excel 97-2003 Workbook *.xls)

Recommended blog posts

Interested in learning more about pivot tables? You must read these blog posts!