## Running totals based on criteria

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:

I highly recommend a pivot table for this task, however, this article demonstrates a formula combined with an Excel defined table. A pivot table is lightning fast if you have lots of data to work with and is easy to learn.

Formula in cell G6:

The SUMIFS function was introduced in Excel 2007, use the SUMPRODUCT function if you have an earlier Excel version.

### How to create an Excel defined table

- Select a cell in the data set.
- Press CTRL + T
- A dialog box appears, enable checkbox if your data set has headers.

- Click OK.

### Explaining formula in cell G6

Some of the cell references in the SUMIFS function are structured references pointing to a range in an Excel defined table. Structured references adjust automatically when data is added or removed to the table, the formula will instantly return the new running total.

Structured reference -> Table1[Amount]

The SUMIFS function adds numbers based on criteria and returns the total.

SUMIFS(*sum_range*,* criteria_range1*, *criteria1*, *[criteria_range2], [criteria2], *...)

#### Step 1 - First argument is sum_range

The sum_range argument is a structured reference: Table1[Amount]. This cell ranges contains the numbers to be added based on critera.

#### Step 2 - Second and third argument

The first pair of arguments evaluates if cells in column **Client** equal value in cell G4.

Argument |
Cell reference |

criteria_range1 |
Table1[Client] |

criteria1 |
G4 |

#### Step 3 - Fourth and fifth argument

The second pair of arguments evaluates if cells in column **Date **equal or are less than value in cell G3.

Argument |
Cell reference |

criteria_range2 |
Table1[Date] |

criteria2 |
"<="&G3 |

The less than and equal sign are concatenated to cell reference G3 using the ampersand character.

#### Step 4 - Sixth and seventh argument

The third pair of arguments evaluates if cells in column **Date **equal or are larger than value in cell G2.

Argument |
Cell reference |

criteria_range3 |
Table1[Date] |

criteria3 |
">="&G2 |

The larger than and equal sign are concatenated to cell reference G2 using the ampersand character.

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

The formula in cell D3 adds all unique numbers in cell range B3:B12 and returns the total. Unique values are all […]

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]

To extract groups from cell range B3:B10 I use the following regular formula in cell B13. Weekly Blog EMAIL Email Welcome! […]

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

The SUM function in cell D3 uses only a single cell reference and still manages to sum current and previous […]

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]

### 9 Responses to “Running totals based on criteria”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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