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.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
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.
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