SUMIFS function in Excel

You already know how to sum in Excel with the SUM () function, but to sum cells that satisfy many given conditions, you may not know it. The SUMIFS () function helps you quickly sum cells in a range that satisfy one or more conditions.

SUMIFS function in Excel Picture 1

The following article will describe the syntax, functions, and usage of the SUMIFS () function in Excel.

Description

The SUMIFS () function is a function that sums cells in a range, the selection range meets one or more conditions.

Syntax

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

Inside:

- sum_range: is the cells to sum including numbers or names, ranges or cell references containing numbers, blank values ​​and text values ​​are ignored. Is required parameters.

- criteria_range1: range to be evaluated by conditions, required.

- criteria1: condition in the form of a number, expression, cell reference, required.

- criteria_range2, criteria2, .: optional additional ranges and conditions, the function allows up to 127 pairs of criteria_range, criteria.

Note

- For each cell in the range, the sum_range range will be summed when all the conditions specified are true for that cell.

- The cells in sum_range containing TRUE are considered to be 1, cells that are not yet FALSE are considered as 0.

- Each criteria_range must have the same size and shape as sum_range, ie criteria_range and sum_range must have the same number of rows and the same number of columns.

- In criteria criteria, you can use wildcards: a question mark (?) Instead of a single character, asterisk (*) instead of a certain string. If the condition is a question mark or asterisk, you must enter an additional ~ before it.

For example

Given the following data sheet:

SUMIFS function in Excel Picture 2

Request:

1. Calculate the total number of products sold by employees named Russia with No. <5.

2. Calculate the total number of products sold by the employee named Huong with all products except "Na".

The results after applying the SUMIFS () function are as follows:

SUMIFS function in Excel Picture 3

So, with the content of this article presented, you can know the syntax, functions and how to use the SUMIFS () function through specific examples. Hope the article will help you. Good luck!