The SUMIF function returns the sum of values for a specific criterion.
This function is better than to perform a filter.
Table of Contents
Common mistake with Excel
When you want to sum a column but for some specifics criteria, most of the users do:
- Filter the column with the criterion
- Select your column where you have the data to sum
- Look in the status bar the result
Ok, it works but if you have a lot of criteria, you can't work like that ⛔⛔⛔
In this case, you need to use the SUMIF function in your worksheet
The SUMIF Function
The SUMIF function needs 3 parameters.
- The range of criteria
- The criteria
- The range to add
Quantity of the pens
For instance, you want to know the number of pens sell in the month.
In H4, write the beginning of the formula and select the range of cell where you have your product.
Then, you write your criteria or the reference where you have your criteria.
or with the reference of the cell.
At this point, there is no difference with the COUNTIF function.
Finish with the range of cells where you have your data to add.
Let's check the result
The result returns 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 returns 245 😉
Sales for the pens only
If you want to return the sum of the sales for the pens, you just have to change the 3rd arguments.
Instead of the column of the quantity (col C), you indicate the column of the sales (col E).
More than one criteria
The SUMIF function is build to make the sum for only one criterion in a single column.
Now, if you want to return the sum for more than one criteria, you must use the function SUMIFS