You have just been assigned the task of calculating a table of numbers. In addition to simple calculations, that data table also has extremely difficult summations with two to three conditions attached. You don't know how to quickly deal with those metrics. So let Web888 guide you to do it quickly and accurately using the SUMIFS function on Excel.

Functions on Excel are very rich and diverse. Taking into account the SUM function alone, there are already three SUM functions. However, each function has different strengths and functions. If the SUM function is used to sum values that do not contain a condition, the SUMIF function uses the sum of values that contain a condition. And today, Tech12h will show you how to sum values containing multiple conditions with the SUMIFS function.

The content of the article consists of 2 parts:

• Introduction to the SUMIFS . function
• For example, I illustrate the SUMIFS . function

1. Introduction to the SUMIFS . function

The SUMIFS function is used to solve sum problems but contains one or more given conditions.

The function syntax is as follows:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

In there:

• sum_range : The range to sum includes numbers or names, the cell reference range containing the numbers. This is a required parameter.
• criteria_range1 : Condition range 1, is the range of cells to compare with the summation condition.
• criteria1 : Condition 1, is the condition to calculate the sum. The value of the condition can be a number, expression, column reference, or string.
• criteria_range2 : Condition 2 range, which is the range of cells to compare with the summation conditions.
• criteria2 : Condition 2, is the condition to calculate the sum….

In addition, the operation can have many other conditions.

Example: The problem asks you to calculate the total amount of money that Tam has sold from the table below:

We have the following calculation formula:

=SUMIFS(D2:D6,E2:E6,"Tam",C2:C6,">50"), we get the following result: 3,000,000

2. Example of SUMIFS . function

We have the following data table:

Request:

• Calculate the total amount of money that Hoang sold with the total amount greater than 4?
• Calculate the total number of products that Hoang can sell excluding handbags with a sales volume greater than 3?

To solve the above problem, do the following:

First, calculate the total amount of money that Hoang sold with an amount greater than 4.

We see, here there are two conditions that you need to define:

• Condition 1: Seller is Hoang
• Condition 2: That amount is only calculated from the product item with STT greater than 4.

Thus, we have the following formula:

=SUMIFS(D2:D6,E2:E6,"Hoang",A2:A6,">4") we get the following result: 1,000,000

Second, calculate the total number of products that Hoang can sell, excluding handbags with a sales volume greater than 3.

Similar to the first sentence, we define the preconditions. Here, there are three conditions:

• Condition 1: Seller is Hoang
• Condition 2: Such products must be excluding bags.
• Condition 3: Calculate the products Hoang sells from No. 3. More than 3.

From there, we have the following calculation formula:

=SUMIFS(C2:C6,E2:E6,"Hoang",B2:B6,"<>Handbag",A2:A6,">3") we get the following result: 5

Note: The sign " <>" that I used above the formula above is the sign used with the exclude function.

Thus, I showed you how to use the SUMIFS function to solve problems containing many different conditions. The above are just normal problems and the calculation data is simple so you can calculate it. However, for more complex problems, using this function will help you a lot in your work. And the next article that I want to share with you is to get acquainted with the COUNT function – the function to count data . If you find it useful, please share to let everyone know more.

Thank you!