Function SUMIF

The SUMIF function returns the sum of values for a specific criterion.

This function is better than to perform a filter.

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.

=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)

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).

=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 posts


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


Leave a Reply

Your email address will not be published.