How to Use SUMIF with Multiple Criteria in an Excel Formula?

Sharing is caring!

Are you looking to sum values in Excel based on multiple criteria? The SUMIF function allows you to easily sum data that meets a single condition. But what if you need to sum values based on two or more criteria? In this article, we’ll cover exactly how to use SUMIF with multiple criteria in Excel to sum values based on multiple conditions.

Understanding the SUMIF Function

Before we dive into using SUMIF with multiple criteria, let’s ensure a solid understanding of what the basic SUMIF function does. SUMIF is an Excel formula that allows you to sum values in a range that meet specified criteria.

The syntax for the SUMIF function is:

=SUMIF(range, criteria, [sum_range])

Here’s what each argument means:

• range: This is the range of cells that you want to test against the criteria. It can be a reference to a range of cells, such as A1:A20, or a named range.
• criteria: This is the criteria that determines which cells in the range to sum. It can be a number, expression, cell reference, or text string. For example, “>1000” would sum values greater than 1000.
• [sum_range]: This argument is optional. It’s the actual cells to sum if their corresponding cells in the range argument meet the criteria. If this argument is omitted, the cells in the range argument are summed.

For example, let’s say you have sales data in columns A (salesperson name) and B (sales amount):

To sum the sales for John, you could use this formula:

=SUMIF(A2:A6,”John”,B2:B6)

This would return $1,750, which is the sum of John’s $1,000 and $750 in sales.

Using SUMIF with Multiple Criteria

The SUMIF function works great if you only need to sum based on a single criteria. But in many real-world scenarios, you may want to sum values that meet two or more conditions, such as summing sales over $1,000 for a specific salesperson.

Unfortunately, the SUMIF function does not allow you to directly specify multiple criteria. However, there’s a workaround. By using the multiplication of two or more SUMIF functions, you can effectively achieve a SUMIF with multiple criteria.

Here’s the general approach:

=SUMIF(range1, criteria1, [sum_range]) * SUMIF(range2, criteria2, [sum_range]) * …

The key here is that multiplying the SUMIF functions together will only sum the values if all criteria are met. This is because multiplying by 0 (which is what you get if a criterion is not met, i.e., FALSE) will always result in 0.

Example: Summing with Two Criteria

Let’s look at an example to solidify this concept. Say you have this sales data with salesperson, region, and amount columns:

To sum sales amounts over $1,000 for the West region, you could use this formula:

=SUMIF(B2:B5,”West”,C2:C5) * SUMIF(C2:C5,”>1000″,C2:C5)

Let’s break this down:

• The first SUMIF sums values in C2:C5 if the corresponding region in B2:B5 is “West”.
• The second SUMIF sums values in C2:C5 that are over 1000.
• Multiplying them together only includes sales that are both in the West region and over $1,000.

So in this example, the formula would return $1,200, which is the amount of John’s sale that meets both criteria.

Example: Summing with Three or More Criteria

You can extend this approach to sum based on three or more conditions by multiplying three or more SUMIF functions together. For example, to add a third criteria to the previous example to only include sales by John, the formula would be:

=SUMIF(A2:A5,”John”,C2:C5) * SUMIF(B2:B5,”West”,C2:C5) * SUMIF(C2:C5,”>1000″,C2:C5)

This would only sum sales by John in the West region that are over $1,000.

SUMIFS: A More Streamlined Solution

While using the multiplication of SUMIF functions works for summing with multiple criteria, it can get a bit cumbersome, especially if you have several conditions.

Fortunately, Excel 2007 and later versions include a function called SUMIFS that is designed specifically for summing with multiple criteria. The syntax is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Here’s what each argument means:

• sum_range: This is the range of cells that you want to sum.
• criteria_range1: This is the first range of cells that you want to check against criteria1.
• criteria1: This is the criteria for criteria_range1.
• [criteria_range2, criteria2],…: These are additional ranges and their associated criteria. They are optional and you can include as many as you need.

Using SUMIFS, the examples from the previous section can be written more cleanly as:

Two criteria:
=SUMIFS(C2:C5, B2:B5, “West”, C2:C5, “>1000”)

Three criteria:
=SUMIFS(C2:C5, A2:A5, “John”, B2:B5, “West”, C2:C5, “>1000”)

The SUMIFS function checks each criteria_range against its corresponding criteria and only includes values in the sum_range if all criteria are met. This makes it much easier and cleaner to sum with multiple criteria compared to using multiple SUMIF functions.

Tips for Using SUMIF(S) with Multiple Criteria

Here are a few tips to keep in mind when using SUMIF with multiple criteria or the SUMIFS function:

1. Ensure Ranges are the Same Size

When using SUMIFS or multiplying SUMIF functions, it’s crucial that all ranges are the same size and shape. The sum_range (or the sum_range in each SUMIF when multiplying SUMIF functions) must be the same size as each criteria_range.

2. Use Absolute Cell References

If you need to copy your formula to other cells, consider using absolute cell references (with the $ sign) for your ranges so they don’t change when copied. For example:

=SUMIFS($C$2:$C$5, $A$2:$A$5, “John”, $B$2:$B$5, “West”, $C$2:$C$5, “>1000”)

3. Criteria Can Be a Cell Reference

Instead of typing the criteria value directly in the formula, you can use a cell reference for the criteria. This is especially helpful if you want to easily change the criteria. For example:

=SUMIFS($C$2:$C$5, $A$2:$A$5, $F$1, $B$2:$B$5, $G$1, $C$2:$C$5, $H$1)

4. Be Careful with Criteria Formatting

It’s important to make sure that the formatting of your criteria matches the formatting of the values in the criteria_range. Excel will do an exact match. For instance, if your criteria_range contains formatted dates, make sure the criteria is a date value too, not a text string.

Advanced Usage: Combining with Other Functions

Once you understand how to use SUMIF with multiple criteria and the SUMIFS function, you can start combining them with other Excel functions for even more powerful analysis.

For example, you could use SUMIFS inside an IF function to conditionally sum values based on multiple criteria. Or you could use SUMIFS with the MONTH or YEAR functions to sum values based on multiple criteria and a specific month or year.

The possibilities are endless when you start combining these functions. It’s a testament to the power and flexibility of Excel for data analysis.

Final Thoughts

Summing values based on multiple criteria in Excel is a common need for many users. While the basic SUMIF function only supports a single condition, you can use the multiplication of multiple SUMIF functions to sum with multiple criteria.

Excel 2007 and later also provide the SUMIFS function which is designed specifically to handle multiple conditions in a more streamlined and intuitive way.

By using these techniques for SUMIF with multiple criteria, you can easily sum data in Excel based on two or more conditions. This is an incredibly powerful way to analyze and summarize your data, and when combined with other Excel functions, the possibilities are limitless.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.