## Running totals within date range

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

You can click on headers to sort table for easy finding.

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

### 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