SUMIFS()

This article is also available in: Bahasa Malaysia Bahasa Indonesia


The SUMIFS() function in Excel provides greater flexibility in data analysis than SUMIF() as it allows you to specify multiple criteria or conditions.

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

sum_range – the range of cells that have the values you want to sum

criteria_range1 – the range that has the condition that you want to check,

criteria1 – the condition that you want to check,

With the SUMIFS() function, you can specify up to 127 pairs of criteria range and criteria.

As an example, if we had a worksheet with daily sales.

daily sales

From this, we want to total up sales for Amirah Abdullah, but only for product QuantumGlow LED Lamp.

As you can see from the worksheet, Amirah has sold other products as well, so we need to filter the total sales for only QuantumGlow LED Lamp.

Start by typing in =SUMIFS( in an empty cell.

sumifs

The first thing we need to do is specify the range where the values are that we want to total.

This will be the sum_range in the formula.

sumifs sum range

In this case the total sales values are in column F, so we select F2 to F20 and type in a comma to move to the next part in the formula.

Next, we need to select the column where we want to search for the first criteria.

This will be the criteria_range1 part of the formula.

sumifs criteria range 1

For criteria_range1, we want to look for Amirah Abdullah, and the names of all sales persons are listed in column B.

Select cell B2 to B20 and enter a comma.

Now we need to enter the value that we are looking for in that range, so type in “Amirah Abdullah” followed by a comma.

sumifs criteria 1

For our second criteria, we want only sales for QuantumGlow LED Lamp.

sumifs criteria range 2

The product names are in column C, so we need to select cells C2 to C20.

Type in a comma, then add the second criteria, “QuantumGlow LED Lamp” and press enter to complete the formula.

sumifs total

Excel then looks through columns B and C for Amirah Abdullah and QuantumGlow LED Lamp, and if found, adds the sales amount to the total as shown in cell I5.

The completed formula will be:

=SUMIFS(F2:F20,B2:B20,"Amirah Abdullah",C2:C20,"QuantumGlow LED Lamp")

sum_range is F2:F20, the sales amount column which we want to total,

criteria_range1 is B2:B20, the column that has all the sales person’s names,

criteria1 is “Amirah Abdullah”, the sales person we want the total sales for,

criteria_range2 is C2:C20, the column with the product names, and,

criteria2 is “QuantumGlow LED Lamp”, the product sales total that we need for Amirah Abdullah.

Try this out

You can now extend this for other sales persons to find out who made the most QuantumGlow LED Lamp sales.

Also try using wild cards for criteria1 and criteria2.

So instead of keying in the sales persons full name, “Amirah Abdullah”, key in “Amirah*” instead and for criteria2, use “QuantumGlow*“.


Did you find this article helpful? Please rate and also share your thoughts in the comments section below.

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

AVERAGE() function in Excel

AVERAGE()



This article is also available in: Bahasa Malaysia Bahasa Indonesia