SUMIF Function (Table of Contents)
SUMIF Function in Excel
In excel we all deal with formulas. In fact, excel is all about formulas and techniques. Like many other functions in excel SUMIF is one of the Math & Trigonometry function excel.
SUMIF means “SUM certain row or column if the given condition or criteria is true or satisfied”.
In SUMIF, we can SUM certain cells or group of cells only based on one criterion. SUMIF is available under Formula bar and under Math & Trigonometry bar.
SUMIF Formula in Excel
The Formula for the SUMIF Function is as follows:
- Range: The column, which includes criteria values.
- Criteria: What is the one thing you need to SUM based on the
- SUM_RANGE: The cells or range of cells that you want to SUM. For Example A1: A20 range.
How to Use the SUMIF Function in Excel?
This SUMIF is very simple easy to use. Let us now see how to use the SUMIF Function with the help of some examples.
Example #1
From the below table calculate the total sales amount for the country Mexico.
We have a sales table from A1: F19. Out of this data, we need to calculate the total sales for the country Mexico.
below is apply the SUMIF formula cell A22 to get the total sales for the country Mexico.
The SUMIF function returns the total sales for country MEXICO as 376110
The formula we used here is
Part 1: this is our Criteria Range part, our criterion is to sum the total sales for country Mexico, and this is in the range B2:B19.
Part 2: This is our Criteria. From our Criteria Range, we need to give Criteria as “Mexico”.
Part 3: This is our SUM column we need to do the summation. Here are the sales range is from F2:F19.
Example #2
Take the same example from the above but calculate the total sales for each country.
Apply the below formula to get the total gross sales.
The SUMIF function returns the total sales for each Country.
This is very similar to our previous formula but there are very minor changes we did.
Part 1: this is our Criteria Range part, our criterion is to sum the total sales for country Mexico, and this is in the range B2: B19. We locked the range using dollar symbols here. Because; our Criteria Range must be standard for all the countries.
Part 2: This is our Criteria. From our Criteria Range, instead of typing each country name manually we have given a cell reference. Here we have not locked the cell because country cell needs to change when we copy paste the paste formula to below cells. That means H2 should change to H3 when copied down the formula and so on.
Part 3: This is our SUM column we need to do the summation. Here are the sales range is from F2: F19. This is also locked because; our Criteria Range must be standard for all the countries
Example #3
Now we know how this formula works. We will move on to the advanced level of this formula. SUMIF can calculate the values based on wildcard characters.
From the above table calculate sales amount for Thomas. They have not mentioned whether it is Sitara Thomas or Abhishek Thomas. Wherever we find Thomas, we need to SUM it.
In these kinds of scenarios, we need to use wildcard entries. Commonly used wildcards are an asterisk ( *) and question mark (?)
Apply the below formula to get the total sales for Thomas.
The SUMIF function returns the total sales for Thomas as 6561.
Now look at the formula in the criteria syntax we placed value “*Thomas*” instead of just “Thomas”. Here asterisk is the wildcard character. Here, asterisk looks for a particular character in the sentence.
Therefore, in Sitara Thomas and Abhishek Thomas case asterisk treats both as one and do the summation of sales amount for both the sales manager together.
Example #4
In SUMIF function, we can add the two different criteria’s in a single criteria range. From the below data calculate the total sales value for the countries France & Canada.
Here we need to apply the same formula but when we are giving criteria, we need to do little changes and apply some techniques.
Firstly, we added the SUM function before adding SUMIFS. The reason we added SUM before the main function is we are giving two criteria’s in a single formula.
- SUMIF first parameter is Criteria Range i.e. “B2: B19”.
- The second parameter is we need to specify which countries we are doing the summation. However, since we are giving multiple criteria first we need to enter curly brackets {} to include our criteria.
- Inside the curly brackets {} we need to specify our two country names in double quotes.
- Last part is our sum range i.e. “F2:F19”.
Firstly SUMIF calculates the sales for the country France (70,650) and secondly calculate the sales for the country Canada (4,53,964).
Once the SUMIF returns the total sales for the two countries, the SUM function will simply add both the countries sales together (70,650 + 4, 53, 964 = 5, 24, 613.5).
Example #5
Use the same table from the above example and calculate a total sale if the unit sold is greater than or equal to 2000.
Apply the same logic which we have used in example 1 but instead of selecting the country range and giving a country name as the criteria select unit sold column as range and give the criteria as “>=2000”.
The SUMIF returns the total sales if the unit sold is >=2000 as 48195.5.
Things to Remember about SUMIF Function
- SUMIF can be applied only to single column criteria. It can satisfy only one-column criteria.
- An error of #Valuewill occurs if the ranges of criteria range and sum range are not of the same range.
- If you want to use multiple criteria’s then use SUMIFS function.
- Numeric values in the criteria parameter need not be enclosed with double quotes. But in case of numeric and text you need to enclose in double quotes “>=2000”, “<=25”, “=555” etc..
- Wildcards are used to match the part of the name or part of the sentence. Asterisk denotes series of character and question marks denotes any single character of the supplied criteria.
Recommended Articles
This has been a guide to SUMIF Function. Here we discuss the SUMIF Formula and how to use SUMIF Function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
