The SUMIF function in Excel calculates the sum based on a specific product, region, or name as a condition.

However, many Excel users struggle to set the function correctly and often make mistakes. Let’s learn how to write and use the SUMIF function to analyze data efficiently.

🔗 At the end of this article, you will find links to test your understanding of the function.

Writing the SUMIF Function

The syntax for the SUMIF function is simple, but understanding each argument is essential to avoid errors.

1. Range: The cells to check for the condition (criteria).
2. Criteria: The condition or rule to match, such as a region or product name.
3. Sum_range (optional): The cells to sum if the condition is met.

=SUMIF(range, criteria, [sum_range])

Examples of the SUMIF Function

For example, let’s calculate the Sales amount for the North region. How do we write this function?

• First, focus on the condition or criteria.
• Start by selecting the column containing the criteria, such as Area.
• Next, define the value of the criteria, for example, North.
• Then, select the column with the values to sum, like the Total column.
• 💡Remember, the last argument must always point to the column with the values to sum.

In this situation, the function

1. Read the column Area
2. For the value North
3. And add each value for the criteria North: 22 + 29.4 + 33.2 = 84.6

Now, if we want to calculate the Sales amount for Apricot, this time, we will write

1. The Fruits column
2. The criteria Apricot
3. And still the same column to add, the Total

Last example, you can also use logical operators (greater, lower) to write your criteria. For instance, if you want to calculate the sum of the sales for July, you can write this function

Practise SUMIF online

Now write your own SUMIF formulas, directly in your browser. A portfolio of 15 sales: calculate the total by salesperson, by region and by product, in 6 steps, free and corrected as you type:

👉 One more, with an operator, as in the last example above: in cell G2, add up the amounts greater than $3,000. This is a real spreadsheet: type in it as you would in Excel.

The answer is =SUMIF(E2:E16,”>3000″). The operator and the number are written together, between double quotes.

Related articles

• Step-by-step guide for SUMIF function
• Training with the SUMIF and SUMIFS functions
• Test Your Skills with COUNTIF and COUNTIFS
• SUMIF vs SUMIFS in Excel: The Trap of the Argument Order

GROUPBY function can replace SUMIF

If you have difficulties writing the SUMIF function correctly, you can use the new GROUPBY function of Excel 365. Not only is this new function easier to write, but it also calculates the sum for each item of the column. GROUPBY can replace SUMIF for exact criteria match, not for lower or greater criteria.