The SUMIFS function, how to use multiple conditional calculation functions in Excel

The SUMIFS function in Excel is one of the basic Excel functions, the calculation function is often used in Excel. To calculate the sum in Excel we will use the SUM function, if we want to add a certain condition to that aggregate function we will use the SUMIF function. In the case of the aggregate request data table with many conditions, we must use the SUMIFS function. The following article will guide you how to use the SUMIFS function in Excel.

1. MS Excel - Lesson 5: Excel formulas and functions
2. How to combine Sumif and Vlookup functions in Excel
3. 3 ways to calculate totals in Excel
4. How to fix the SUM function doesn't add up in Excel

Instructions for using SUMIFS function in Excel

The SUMIFS function syntax in Excel takes the form = SUMIFS (sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .).

Inside:

1. sum_range: is the cells to be summed in the data table, empty values ​​and text values ​​are ignored, required parameters are required.
2. criteria_range1: the range to be checked by the criteria1, is the required value.
3. criteria1: applied condition for criteria_range1, it can be a number, expression, cell reference to determine which cell in criteria_range1 will be summed, which is also a required value.
4. criteria_range2, criteria2, .: optional additional ranges and conditions, up to 127 criteria_range, criteria pairs.

Note when using SUMIFS function in Excel:

1. Each cell in the region, sum_range scope will be summed when all the corresponding conditions specified are correct for that cell.
2. The cells in sum_range that contain TRUE are treated as 1, the cells not yet FALSE are considered 0.
3. Each criteria_range must have the same selection size as sum_range, criteria_range, and sum_range must have the same number of rows and the same number of columns.
4. criteria can use a question mark character (?) instead of a single character, an asterisk (*) replaces a string. If the condition is a question mark or a star, then you must enter the previous ~, the condition value is the text in the sign '.

We will sum it up with the following data table with several different conditions.

Example 1 : Calculating the total products that Hoai's staff sell with the unit price of under VND 400,000

In the input box, we enter the formula = SUMIFS (D2: D7, C2: C7, "Hoai", E2: E7, "<400000") and then press Enter.

In which D2: D7 area needs to calculate the total items, C2: C7 is the condition value area of ​​the employee name, 'Hoai' is the condition that the employee name needs to count the number of items contained in the area C2: C7, E2: E7 is Item condition, '400,000' is the condition that contains the item in area E2: E7.

Results out the number of items as shown below.

Example 2 : Calculate the total number of items sold by Russian employees with a serial number <5.

In the input box, we enter the formula = SUMIFS (D2: D7, C2: C7, "= Russia", A2: A7, "<5") and press Enter.

The result is that the total number of items sold by Russian employees has a number of

Example 3 : Calculate the total number of products sold by Russian employees, except for scarves.

At the input box, we enter the formula = SUMIFS (D2: D7, C2: C7, 'Russia', B2: B7, '<> scarves'). Where <> is used to exclude an object in the data area as a condition.

The results show the total number of products as shown below.

Here are some examples of how to use SUMIFS, calculate the total value with multiple matching conditions. We have to sort out the appropriate areas and conditions, then Excel will recognize the function formula to calculate.

I wish you all success!