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 […]
Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]
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 […]
The easiest way to sum a cell range is to simply select the cell range and read the values in […]
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
To extract groups from cell range B3:B10 I use the following regular formula in cell B13. Related articles Running totals The […]
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
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.
English
Spanish
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