SUMIF function in Excel Formula

Excel is a powerful tool for data analysis and manipulation, and one of the most useful functions in Excel is the SUMIF function. This function allows you to add up values in a range of cells that meet certain criteria. In this article, we will explain how the SUMIF function works, and provide examples in table format.

Syntax of the SUMIF function

The syntax of the SUMIF function is as follows:

SUMIF(range, criteria, [sum_range])

Where:

range: The range of cells that you want to evaluate for the criteria.

criteria: The criteria that the cells must meet in order to be included in the sum.

sum_range (optional): The range of cells that you want to add up. If this argument is omitted, the cells in the range argument will be added up.

Example 1: Simple SUMIF function

Suppose we have the following table of data:

Name	Score
Alice	85
Bob	70
Carol	90
Dave	80
Eve	75

If we want to sum the scores of all the students who scored above 80, we can use the SUMIF function with the following formula:

=SUMIF(B2:B6,">80",B2:B6)

In this formula, the range is B2:B6 (the range of scores), the criteria is ">80" (scores above 80), and the sum_range is also B2:B6 (the range of scores). The result is 175, which is the sum of Carol's score (90) and Dave's score (80).

Example 2: SUMIF function with wildcards

Suppose we have the following table of data:

Name	Product
Alice	Apple
Bob	Banana
Carol	Apple
Dave	Pear
Eve	Banana

If we want to sum the number of apples sold, we can use the SUMIF function with a wildcard (*) to match any product that contains the word "apple":

=SUMIF(B2:B6,"apple",C2:C6)

In this formula, the range is B2:B6 (the range of products), the criteria is "apple" (any product that contains the word "apple"), and the sum_range is C2:C6 (the range of sales). The result is 2, which is the sum of the number of apples sold by Alice and Carol.

Example 3: SUMIF function with multiple criteria

Suppose we have the following table of data:

Name	Age	Score
Alice	25	85
Bob	30	70
Carol	25	90
Dave	35	80
Eve	30	75

If we want to sum the scores of all the students who are 25 years old and scored above 80, we can use the SUMIF function with the following formula:

=SUMIFS(C2:C6,B2:B6,25,C2:C6,">80")

In this formula, the range is C2:C6 (the range of scores), the first criteria is B2:B6,25 (students who are 25 years old), and the second criteria is C2:C6,">80" (students who scored above 80). The result is 85, which is the score of Alice.

Conclusion

The SUMIF function is a useful tool for adding up values that meet certain criteria