SUMIF Function in Excel

SUMIF Function in Excel
Last Updated on 25/08/2023
Reading time: 2 minutes

The SUMIF function returns the sum of values for a specific criterion. It's one of the most useful functions in Excel

  1. The column containing the criterion

    Select the column where is the first criterion

  2. The criterion

    Write the value of the first criterion

  3. Select the column to sum

    Only one column must be selected. This column must contain numeric values.

=SUMIFS(Column with the criterion, Criterion, Range to add)

Never do this to SUM specific rows ⛔

When you want to sum a column for a specific criterion, some users do this:

  1. Filter on the criterion you want in column B
  2. Select the column where you have the data to sum (Column C)
  3. Look in the status bar for the result
Read the sum in the status bar

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 😀

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.

=SUMIF($B$3:$B$12,

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.

=SUMIF($B$3:$B$12,"Pen"

Or with a cell reference.

=SUMIF($B$3:$B$12,$G4

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.

=SUMIF($B$3:$B$12,$G4,$C$3:$C$12)

SUMIF return the amount of quantity for the products

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

=SUMIF($B$3:$B$12,$G4,$E$3:$E$12)

Amount of the sales for pens

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

Result for all the porduct after copy paste

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

Function SUMIFS with 2 criteria

Leave a Reply

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

SUMIF Function in Excel

Reading time: 2 minutes
Last Updated on 25/08/2023

The SUMIF function returns the sum of values for a specific criterion. It's one of the most useful functions in Excel

  1. The column containing the criterion

    Select the column where is the first criterion

  2. The criterion

    Write the value of the first criterion

  3. Select the column to sum

    Only one column must be selected. This column must contain numeric values.

=SUMIFS(Column with the criterion, Criterion, Range to add)

Never do this to SUM specific rows ⛔

When you want to sum a column for a specific criterion, some users do this:

  1. Filter on the criterion you want in column B
  2. Select the column where you have the data to sum (Column C)
  3. Look in the status bar for the result
Read the sum in the status bar

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 😀

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.

=SUMIF($B$3:$B$12,

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.

=SUMIF($B$3:$B$12,"Pen"

Or with a cell reference.

=SUMIF($B$3:$B$12,$G4

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.

=SUMIF($B$3:$B$12,$G4,$C$3:$C$12)

SUMIF return the amount of quantity for the products

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

=SUMIF($B$3:$B$12,$G4,$E$3:$E$12)

Amount of the sales for pens

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

Result for all the porduct after copy paste

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

Function SUMIFS with 2 criteria

Leave a Reply

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