 # SUMIF Function in Excel #### Frédéric LE GUEN

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

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)

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

## 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

# SUMIF Function in Excel

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

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)

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

## 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