«

»

Function SUMIF

The SUMIF function works like the COUNTIF function. But here, you don't count the number of rows. The function will make a sum for a sample of your data.

Purpose of the SUMIF function

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.

=SUMIF(B3:B12;

Then, you write your criteria or the reference where you have your criteria.

=SUMIF(B3:B12;"Pen";

or with the reference of the cell.

=SUMIF(B3:B12;G4;

At this point, there is no difference with the COUNTIF function.

Finish with the range of cells where you have your data to add.

=SUMIF(B3:B12;G4;C3:C12)

The result is 245

  • 75 (row 3)
  • 50 (row 6)
  • 100 (row 8 )
  • 20 (row 12)

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 parameters. Instead of the column of the quantity (col C), you indicate the column of the sales (col E).

=SUMIF(B3:B12;G4;E3:E12)

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

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/sumif/


Leave a Reply

Your email address will not be published. Required fields are marked *