## Running totals within date range in excel

LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, I have to **match a client name**, and **add all the sales totals**:

clientA 10

clientA 10

clientA 10

clientB 5

clientB 5

clientB 5

So if I search for clientA, I need one cell that keeps a running total as sales are added. Lastly,** a date range** will need to be given, so search for sales from clientA between two dates and** keep a running total**...

### Answer:

### Excel formula in cell I3:

### Explaining excel formula

**Step 1 - Find records equal or larger than the first date criterion**

=SUMPRODUCT(**(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)***(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)*(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)*D3:INDEX(D3:D10000, MATCH(9,99999999999999E+307, B3:B10000)))

MATCH(9,99999999999999E+307, B3:B10000) finds last cell in column and returns the relative position (row number). This makes the range expand when new values are added.

INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000)) creates a cell reference to the last cell in column.

B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000)) creates a cell reference to the entire cell range.

(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)

becomes

(B3:INDEX(B3:B10000, 23)>=F3)

becomes

(B3:B25>=F3)

becomes

{40544; 40547; 40550; 40552; 40557; 40558; 40563; 40564; 40567; 40568; 40569; 40573; 40576; 40576; 40579; 40579; 40584; 40589; 40594; 40595; 40598; 40600; 40604}>=40544

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

**Step 2 - Find records equal or smaller than the second date criterion**

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)***(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)***(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)*D3:INDEX(D3:D10000, MATCH(9,99999999999999E+307, B3:B10000)))

(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)

becomes

B3:B25<=G3

becomes

{40544; 40547; 40550; 40552; 40557; 40558; 40563; 40564; 40567; 40568; 40569; 40573; 40576; 40576; 40579; 40579; 40584; 40589; 40594; 40595; 40598; 40600; 40604}>=40574

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

**Step 3 - Find records equal to client criterion**

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)*(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)***(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)***D3:INDEX(D3:D10000, MATCH(9,99999999999999E+307, B3:B10000)))

(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)

becomes

(C3:C25=H3)

becomes

({"Client D"; "Client A"; "Client C"; "Client D"; "Client D"; "Client A"; "Client C"; "Client B"; "Client D"; "Client A"; "Client A"; "Client B"; "Client A"; "Client D"; "Client A"; "Client B"; "Client A"; "Client D"; "Client C"; "Client A"; "Client A"; "Client B"; "Client A"}="Client D")

and returns

{TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}

**Step 4 - Multiply array with amounts and calculate sum**

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)*(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)*(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)***D3:INDEX(D3:D10000, MATCH(9,99999999999999E+307, B3:B10000))**)

becomes

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)*(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)*(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)*{550; 380; 130; 130; 660; 180; 200; 380; 810; 590; 770; 830; 290; 220; 880; 380; 140; 150; 180; 920; 890; 830; 520})

becomes

=SUMPRODUCT({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}*{TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}*{550; 380; 130; 130; 660; 180; 200; 380; 810; 590; 770; 830; 290; 220; 880; 380; 140; 150; 180; 920; 890; 830; 520})

becomes

=SUMPRODUCT({1; 0; 0; 1; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}*{550; 380; 130; 130; 660; 180; 200; 380; 810; 590; 770; 830; 290; 220; 880; 380; 140; 150; 180; 920; 890; 830; 520})

becomes

=SUMPRODUCT({550;0;0;130;660;0;0;0;810;0;0;0;0;0;0;0;0;0;0;0;0;0;0})

and returns 2150 in cell I3.

**Download excel example file**

Running totals within date range.xls

(Excel 97-2003 Workbook *.xls)

**Functions:**

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**SUMPRODUCT(**array1, array2,** )**

Returns the sum of the products of the corresponding ranges or arrays

### Category: Excel

How to return multiple values using vlookup in excel

This post explains how to lookup a value and return multiple values. No array formula required.Comments(441) Filed in category: Excel, VLOOKUP and return multiple values

Lookup and return multiple values concatenated into one cell

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. […]Comments(249) Filed in category: Concatenate, Excel, Textjoin

How to create a dynamic chart (excel 2003 and 2007)

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: […]Comments(161) Filed in category: Charts, Excel, Interactive

### 9 Responses to “Running totals within date range in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Oscar,

Thank you so much!!!! Not only did this solve my issue, I have now learned a lot more about these functions to use on future projects.

Your site is a fabulous resource!!

Thank you again.

~Andrew

i think using only sumproduct formula too easy..

=SUMPRODUCT((B3:B25>=F3)*(B3:B25<=G3)*(C3:C25=H3)*(D3:D25))

mustafa,

The cell references have to be expanding as new records are added, in order to keep a running total.

Hello again Oscar,

I am playing with this some more, and I using the Microsoft Date and Time Picker (Excel 2010) to use for the two dates. I appear to have all the dates matching (dd/mm/yyyy) on both the PO list and my date range cells. However, the formula doesn't seem to be using the dates anymore?

Any thoughts?

Thanks.

~Andrew

Andrew Matheson,

Send me an excel file without sensitive information: Contact me

and I´ll se what I can do.

Looks like an easy pivot table solution to me.

David Hager,

As far as I know, pivot tables are not dynamically updated when new values are added.

The SUMPRODUCT function is easy to understand, it´s the dynamic ranges that make it look complicated.

Now I know: How to create a dynamic pivot table and refresh automatically in excel

For above example how to get date when for client d total exceeded $1200 through formula without helper cells.

Thanks