The SUMIF function returns the sum of values for a specific criterion. SUMIF or SUMIFS is one of the most valuable functions of Excel
Never work like this⛔⛔⛔
When you want to sum a column for a specific criterion, some users do this:
- Filter the column with the criterion (here, column B)
- Select the column where you have the data to sum (Column C)
- Look in the status bar for the result
Ok, it works, but this technique is not the most convenient if you have many results to return.
This is why, in this situation, you must use the SUMIF function 😀
Presentation of the SUMIF function
The SUMIF function is one of the oldest functions in Excel. All the versions have it
The SUMIF function requires 3 arguments.
- The range of criteria
- The criteria
- The range to sum
What is the number of pens sold in the month?
For instance, you want to know the number of pens sold monthly.
Step 1: Select the column containing your criteria
In H4, write the beginning of the formula and select the range of cells where you have your product.
The $ are not necessary yet. They will be important when we will copy this formula later.
Step 2: Write your criteria
Then, you write your criteria or the reference where you have your criteria.
Or with a cell reference.
At this point, there is no difference with the COUNTIF function.
Step 3: Select the column with the values to SUM
Finish with the range of cells where you have your data to add.
Let's check the result
The result returned by the function is 245. In the very first picture of the article, we have
- 75 in row 3
- 50 in row 6
- 100 in row 8
- 20 in row 12
And the sum of these values is 245 😉👍
What are the Sales for the pens only
If you want to return the sum of the sales for the pens, you have to change the 3rd argument.
Instead of the column of the quantity (col C), you indicate the column of the sales (col E).
Result for all the products
Now, we just have to copy the 2 formulae for the other cells and we have the following result
The reference of the ranges of the products, quantity, and Total don't move because of the $.
And if you want more than one criterion?
The SUMIF function is built to make the sum for only one criterion. If you want to return the sum for more than one criterion, you must use the function SUMIFS.