I was recently on a Teams meeting with some associates of mine and we were reviewing unit sales over the prior months. While sharing my screen, I quickly employed Excel formulas to facilitate some quick analysis and discussion, and this helped plan our next steps. After the call, one of the participants called me and asked me to share those formulas with him as he was not familiar with them.
It was apparent to me that while we may all use Excel on a daily basis, our skills and capabilities can be vastly different. In this blog post, I'm sharing some ways you can easily sum different columns of data based on certain conditions.
Source Data
Let's take a look at a fictional data set of widget sales made in January through June in NY, NJ, and CT. Here is a our sample data; it will be used on all of the examples in this post.
SUM
Most people are familiar with SUM - It adds up the values in a range of cells. If we want to quickly find the total unit sales and put that total in Cell F1, we could use the SUM function. There are 2 options we can use for the formula in F1:
=SUM (C2:C19)
=SUM (C:C)
=1158
Both approaches will come up with the same total , however each formula behaves differently:
The former will only include items in Row 2 through Row 19, and will automatically adjust based on row inserts/deletions). Other data added in Rows 20 and beyond would not be included.
The latter will be dynamic such that any value in Column C will be included, even if it is not intended. Perhaps a value in Cell C599.
There is no single right answer to which formula approach to use.
SUMIF
But what if you wanted to find sales in a given month? Or, sales in a given State? To do this, we have to add a single "condition" to the formula. SUMIF is an easy way to do this.
The syntax is: = SUMIF (Range, Criteria, SumRange), where:
Range = The column or row for the conditional validation
Criteria = The specific value to match upon
SumRange = The column or row with the values which will be added (summed) for those cells where the Criteria is met
Using the data above, if Jan was in Cell G7, and the Jan Unit Sales count were to be put in Cell H7, the formula in H7 would be:
= SUMIF (Range, Criteria, SumRange)
= SUMIF (A:A,G7,C:C)
= 194
Upon calculation, Excel will look at Column A (all of the months), and for those where the values match to what is in Cell G7 (Jan), add the corresponding values in Column C.
Using this approach, you can build out a table like this to show the Unit Sales for each month. You can see the formula for Feb in Cell H8 = SUMIF (A:A,G8,C:C) which = 182.
The formula for Unit Sales by State (ex: NY in Cell G15) is similar, however since the State is in Column B, the formula would reflect that Range accordingly:
= SUMIF (Range, Criteria, SumRange)
= SUMIF (B:B,G15,C:C)
= 403
SUMIFS
BUT... what if you wanted to look at your data across multiple dimensions? What if you wanted to look at sales By Month and By State? In this case, you need to look at SUMIFS which allows for multiple Range/Criteria sets to be defined.
Let's presume you wanted to look at your data in a two-dimensional matrix like this:
In this case, using SUMIFS help you find the Unit Sales for a given Month (in Column F) and a given State (in Row 21).
The syntax is: = SUMIFS (SumRange, Criteria_Range1, Criteria1, Criteria_Range2, Criteria2, Criteria_RangeX, CriteriaX)
SumRange = The column or row with the values which will be added (summed) for those cells where the Criterion is met
Criteria_RangeN = The column or row for the conditional validation
CriteriaN = The specific value to match upon
With SUMIFS, there must be both a Criteria_Range1 and a Criteria1, however a 2nd, 3rd, or Nth is optional. Our example has 2 Range/Criteria sets (Month, State), however 3 or more can certainly be used -- provided your source data is structured in a way to support that analyis.
The formula for SUMIFS in Cell G22 for Unit Sales in Jan in NY is:
= SUMIFS (SumRange, Criteria_Range1, Criteria1, Criteria_Range2, Criteria2)
= SUMIFS ($C:$C, $A:$A, $F22, $B:$B, G$21)
= 51
Note: Since we're going to copy these formulas to other columns and rows, and we need to keep our reference to the Criteria_Range in Column F and Row 21, we use the $ in the formula to ensure that Excel uses an "absolute cell reference" and does not change Column and Row when copying/pasting the formula.
Here is our complete analysis of Unit Sales by Month by State. You can see the formula for sales in Feb in NJ.
Summary
SUM, SUMIF, and SUMIFS are quick ways to sum your structured data across a number of different dimensions.
Another way to accomplish this is by creating an Excel Pivot Table. We'll take a look at that in another post.
Comments