Sum a Column Based on Values in Another

In this post, we’ll learn how to add up a column of numbers based on the values in another column. For example, we are trying to analyze product sales based on average customer rating. That is, customers rate our products on a scale of 1 to 10, and so each product has an average rating such as 9.8, 7.2, 6.1, and so on. We would like to compute the sales totals for products rated from 9 to 10, and 7 to 8, and so on.

Just as with anything in Excel, there are multiple ways to accomplish this. But, I wanted an opportunity to talk about how the SUMIFS function supports the use of comparison operators, and this is a great example. And, since COUNTIFS also supports comparison operators, we may as well compute the number of products in each group as well.

The idea for this post came out of an email exchange with Eileen, who asked a question about adding a fee column based on the results in score column. Thanks Eileen!

Objective

Before we jump right into the formulas, let’s take a quick look at our data so we can be clear about our goal. Our data includes the product ID, the average rating, and the total sales for the period. The data is stored in a table named Table1 … like this:

We are trying to compute sales for each product group. “Excellent” products are rated 9 to 10, “Great” products are 7 to 8, “Average” products are 5 to 6, “Poor” products are 3 to 4, and “Bad” products are 1 to 2. Our summary should basically look like this:

Now that we can see our endgame, let’s connect the dots and use SUMIFS and COUNTIFS. I’ve prepared a video and written narrative that provide the details.

Video

Narrative

We’ll use SUMIFS and COUNTIFS to compute our summary values. Let’s just take them one at a time.

SUMIFS

Basically, the SUMIFS function is designed to add up a column of numbers, but, include only those rows that meet one or more conditions. Each condition is defined by a pair of arguments. Here is the basic syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, ...)

Where:

• sum_range is the column of numbers to add
• criteria_range1 is the first criteria column
• criteria1 is the first criteria value
• … additional pairs of arguments

I’ve come up with a little narrative that helps me keep the function arguments straight, and hopefully, it can help you too. Basically, each “this” in the following narrative represents an argument: “Add up this column of numbers, but only include those rows where this column is equal to this value.”

Let’s see if we can apply this function to our summary report. Here is the summary report structure:

Let’s take it one step at a time, and start by writing a formula in C7 that adds up the sales column, but only includes products with a rating equal to 9. This isn’t exactly what we want (we really want greater than 9, not equal to 9), but it will help us get the basic arguments into the function.

The data table is named Table1. So, using our narrative sounds like this “add up the Sales column, but only include those rows where the Rating column is equal to 9.”  Thus, our first formula looks like this:

=SUMIFS(Table1[Sales], Table1[Rating],9)

We hit Enter, and get 0. Why? Because there are no products with a rating equal to 9. So, how do we update the formula to include all products with a rating of 9 or more? Here is the answer and the entire point of this post:

• The SUMIFS function supports comparison operators in criteria value arguments

Said more simply: we can use greater than and less than logic. Greater than is represented by the > comparison operator, and less than uses the < operator. Greater than or equal to is like this >=, and less than or equal to like this <=.

So, we can make one small tweak to our formula by including the greater than or equal to operator >= in the criteria argument. Now, this all needs to be enclosed in quotes, so, the updated version of the formula is here:

=SUMIFS(Table1[Sales], Table1[Rating], ">=9")

We hit Enter and this time … yes, it worked!

So far, Excellent sums all products with a rating that is greater than or equal to 9.

Note: in practice, I’d probably enter the value 9 into a cell rather than in the formula, and then update the formula to reference the cell. For example, if 9 was entered in cell A1, the updated formula would be: =SUMIFS(Table1[Sales], Table1[Rating], “>=”&A1)

Now, let’s think about the next category: Good. This needs to include products with ratings of 7 and 8. But, let’s be more precise. It needs to include products with a rating that is greater than or equal to 7 and less than 9. Make sense? Remember, our ratings include decimals, so we need to handle ratings of 7.1 and 8.5, etc. So, the formula for C8 is:

=SUMIFS(Table1[Sales],Table1[Rating],">=7",Table1[Rating],"<9")

Note: we referenced the Rating column twice. Can we do that? Yes! And, the SUMIFS function uses AND logic, so, all conditions must be true to be included in the sum. If you need to use OR logic instead, check out this post.

And we just continue like that to complete the summary. For example, the formula for Average products is:

=SUMIFS(Table1[Sales],Table1[Rating],">=5",Table1[Rating],"<7")

And, so on until we complete our summary:

Now, all that remains is to compute the Quantity column:

For that, we’ll use the COUNTIFS function.

FREE: Excel Speed Challenge

If you enjoyed this post, please check out our free Excel speed challenge.

Watch one short Excel video a day for 5 days. Total video time is only 45 minutes. Learn the Excel skills that can help you save an hour a week.

COUNTIFS

COUNTIFS basically works the same as SUMIFS, but it returns the count of the number of rows that satisfy the condition/s. As there is no column of numbers to add, we just use pairs of arguments to define the condition/s (there is no sum_range argument).

So, to compute the number of Excellent products, we would use this in D7:

=COUNTIFS(Table1[Rating],">=9")

Then, Good products in D8:

=COUNTIFS(Table1[Rating],">=7",Table1[Rating],"<9")

And so on down the summary. The finished summary is here:

Yay … we did it! And that is how to sum (and count) a column of numbers based on the values in another column.

The formulas are provided in this sample file: ComparisonOperators.xlsx