Advertisement

Using the Excel SUMIF Function

The SUMIF function finds all of the values in a range that meet the criteria you specify and either adds them or adds the values in a corresponding range.

Syntax:

SUMIF( rangecriteria[range to sum] )

Range - The range of values to test the criteria against. Also the range of values to sum if no "range to sum" is specified.

Criteria - The condition that must be met for a value to be included in the sum.

Range to sum - The range of values to be summed (optional ).

The criteria can be a date, a time, an integer, a decimal, a logical (TRUE or FALSE), text, or a logical expression. A logical expression is an expression that includes a comparison operator, for example ">10".  If your logical expression references a cell, place an ampersand(&) before the cell address ("<"&A1). Text and logical expressions must be enclosed in quotes.  Text criteria is not case sensitive, so "DOG" and "dog" are equivalent.

You can use wildcards with text criteria. The asterisk  (*) matches any sequence of characters. For example, ABC*123 matches, ABC---123, ABC-/@4-123 and ABC+123. The question mark (?) matches any single character. For example, ABC?123 matches, ABC-123, ABC/123 and ABCD123. It will not match ABC---123 or ABC-/@4-123. If you want to match the asterisk (*) or the question mark, place a tilde (~) before the character.

Example 1: You have a list of incomes in cells B2 through B11. You want to find the total of all incomes that are less than 100,000 dollars.

=SUMIF(B2:B11,"<100000")

Excel: SUMIF example 1

Example 2: You have two teams: Team One and Team Two. You want to find the total score for Team One. The team names are in cells B2 through B11. The scores are in cells C2 through C11.

=SUMIF(B2:B11,"Team One",C2:C11)

Excel: SUMIF example 2

Example 3: You have two sales regions: Region One and Region Two. They report sales monthly. The fields are labeled "January - Region One," "February - Region One," and so on. You want to find the total sales for Region One without regard to month. The region names are in cells A2 through A13. The sales are in cells B2 through B13.

=SUMIF(A2:A13,"*Region One",B2:B13)

Excel: SUMIF example 3

Example 4: You have a list of people. Their ages are listed in cells B2 through B11.  They all have a score. Their scores are listed in cells C2 through C11. You want the total of the scores for people over the age stored in cell F1.

=SUMIF(B2:B12,">"&F1,C2:C12)

Excel: SUMIF example 4

 

In the following worksheet, all of the cells with orange text contain a calculation. Double-click in the field to display the formula. Feel free to use the worksheet to experiment in any way you like.



Leave a Comment



HTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>