Sum by group
To extract groups from cell range B3:B10 I use the following regular formula in cell B13.
Copy cell B13 and paste to cell B14. Read this article to learn how this formula works.
To sum the numbers by group or value simply use this formula in cell C13.
Copy cell C13 and paste to cell C14.
Explaining formula
The SUMIF function sums cells that meet a condition, the first argument is the range you want to apply the criteria on. In this case cell range $B$3:$B$10, the $ (dollar signs) make the cell reference locked so when the formula is copied the reference stays the same.
The second argument is the criteria found in cell C13. That cell reference has no $ (dollar sign) so it changes when the formula is copied to the cell below. Why? We want it to use another group as a criterion in the next cell.
The third argument $D$3:$D$10 is the actual values we want to sum since they point to the same cell reference no matter where the formula is. It has a $ (dollar sign) that make it an absolute cell reference.
I highly recommend a pivot table if you have lots of data to work with.
Download excel *.xlsx file
Related articles
The SUM function in cell D3 uses only a single cell reference and still manages to sum current and previous […]
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
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 […]
Running totals based on criteria
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
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 […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
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 […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Count text string in a range (case sensitive)
Question: How do I count the number of times a text string exists in a column? The text string may […]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
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