How to Use SUMIFS and AVERAGEIFS in Excel

How to Use SUMIFS and AVERAGEIFS in Excel

Excel is a powerful tool for working with data, and it has many functions that can help you perform calculations, analyze information, and create reports. Two of these functions are SUMIFS and AVERAGEIFS, which allow you to sum or average a range of cells based on multiple criteria. In this article, I will explain how to use these functions and provide some examples of their applications.

What are SUMIFS and AVERAGEIFS?

SUMIFS and AVERAGEIFS are functions that belong to the category of math and trig functions in Excel. They are similar to the SUMIF and AVERAGEIF functions, which can sum or average a range of cells based on one criterion. However, SUMIFS and AVERAGEIFS can handle more than one criterion, up to 127 criteria in total.

The syntax of the SUMIFS function is:

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

The syntax of the AVERAGEIFS function is:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

The arguments of these functions are:

• sum_range or average_range: The range of cells to sum or average. This is a required argument.
• criteria_range1, criteria_range2, …: The ranges of cells that contain the criteria to be evaluated. Each criteria_range must be the same size and shape as the sum_range or average_range. The first criteria_range is required, the rest are optional.
• criteria1, criteria2, …: The criteria that define which cells in the corresponding criteria_range will be included in the calculation. Each criterion can be a number, a text string, a cell reference, or an expression. The first criterion is required, the rest are optional.

How to Use SUMIFS and AVERAGEIFS?

To use SUMIFS and AVERAGEIFS, you need to enter the function in a cell and provide the appropriate arguments. You can type the function manually or use the Insert Function dialog box to help you. Here are some examples of how to use these functions with different types of criteria.

Example 1: Sum or Average Based on Exact Match

Suppose you have a table of sales data like this:

Product	Region	Sales
A	North	100
B	North	200
C	North	300
A	South	400
B	South	500
C	South	600

If you want to sum or average the sales of product A in the North region, you can use these formulas:

=SUMIFS(C2:C7,A2:A7,“A”,B2:B7,“North”)

=AVERAGEIFS(C2:C7,A2:A7,“A”,B2:B7,“North”)

The result is 100 for both formulas.

Note that the criteria are entered as text strings in quotation marks. If you want to refer to a cell that contains the criterion, you can omit the quotation marks. For example:

=SUMIFS(C2:C7,A2:A7,D1,B2:B7,D2)

=AVERAGEIFS(C2:C7,A2:A7,D1,B2:B7,D2)

Where D1 contains “A” and D2 contains “North”.

Example 2: Sum or Average Based on Comparison Operators

You can also use comparison operators such as <, >, <=, >=, <> in your criteria to sum or average based on a range of values. For example, if you want to sum or average the sales of products that have sales greater than 300, you can use these formulas:

=SUMIFS(C2:C7,C2:C7,“>300”)

=AVERAGEIFS(C2:C7,C2:C7,“>300”)

The result is 1500 for the sum and 500 for the average.

Note that when you use comparison operators in your criteria, you need to enclose them in quotation marks along with the value. If you want to refer to a cell that contains the value, you can use the ampersand (&) operator to concatenate it with the quotation marks. For example:

=SUMIFS(C2:C7,C2:C7,“>”&D3)

=AVERAGEIFS(C2:C7,C2:C7,“>”&D3)

Where D3 contains 300.

Example 3: Sum or Average Based on Wildcard Characters

You can also use wildcard characters such as ? and * in your criteria to sum or average based on partial matches. The question mark (?) matches any single character and the asterisk (*) matches any sequence of characters. For example, if you want to sum or average the sales of products that start with B or end with C, you can use these formulas:

=SUMIFS(C2:C7,A2:A7,“B*”)

=AVERAGEIFS(C2:C7,A2:A7,“B*”)

=SUMIFS(C2:C7,A2:A7,“*C”)

=AVERAGEIFS(C2:C7,A2:A7,“*C”)

The result is 700 for the sum and 350 for the average of products that start with B, and 900 for the sum and 450 for the average of products that end with C.

Note that when you use wildcard characters in your criteria, you need to enclose them in quotation marks. If you want to find an actual question mark or asterisk, you need to type a tilde (~) before the character. For example:

=SUMIFS(C2:C7,A2:A7,“~?”)

=AVERAGEIFS(C2:C7,A2:A7,“~?”)

The result is 0 for both formulas, since there are no products that have a question mark in their name.

Conclusion

SUMIFS and AVERAGEIFS are useful functions that can help you perform calculations based on multiple criteria. You can use different types of criteria such as exact match, comparison operators, and wildcard characters to suit your needs. You can also combine these functions with other Excel functions to create more complex formulas. For more information and examples of these functions, you can visit the Microsoft Support website¹² or watch this video tutorial³. I hope you found this article helpful and informative. Thank you for reading!

Tweet