How to sum multiple rows in Excel based on criteria.

By SpreadCheaters
Page last updated: 27/04/2023 | Next review date: 27/04/2025
close

Author bio


SpreadCheaters
View More north_east

As you work with data in Excel, you may often need to sum multiple rows based on specific criteria. For example, you may need to calculate the total sales for a specific product or for a certain region. In this tutorial, we’ll take a closer look at how to use these functions to sum multiple rows based on criteria. 

Here we have a dataset that contains data about the number of Products sold in the Northern and Southern regions. We will find the sum of sold products using two different types of criteria by following the steps below. First, let’s have a look at the dataset above first.

Method – 1 Single Criterion. 

Step – 1 Type the formula.

  • Type the formula in the cell you want to find the sum.
  • Syntax of the formula is:

SUMIF(range, criteria, [sum_range])

range: The range of cells to evaluate.

criteria: The criteria used to determine which cells to add.

sum_range: The range of cells to sum.

  • In our case formula will be:

=SUMIF(C2:C7, “North”, B2:B7)

Step – 2 Apply the formula.

  • Once you type the formula in the cell, hit enter.
  • The sum of values will appear according to the given criterion.
  • In our case, the criteria is to add all the sales in the North region.

Method – 2 Multiple Criteria.

Step – 1 Type the formula.

  • Type the formula in the cell you want to find the sum.
  • Syntax of the formula is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2])

Where;

sum_range: The range of cells to sum.

criteria_range1: The range of cells to evaluate for the first criteria.

criteria1: The criteria used to determine which cells to add for the first criteria.

[criteria_range2, criteria2]: Additional ranges and criteria to add.

  • In our case formula will be:

=SUMIFS(B2:B7, C2:C7, “North”, B2:B7, “>100”)

Step – 2 Apply the formula.

  • Once you type the formula in the cell hit enter.
  • The sum of values will appear according to the given criterion.
  • In our case, the criteria is to add all the sales in the North region that are above 100.