Using of SUMIFS function in Excel

SUMIFS function

Adds the cells in a range that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A9 only if the corresponding numbers in B1:B9 are greater than zero (0) and the corresponding numbers in C1:C9 are less than 10, you can use the following formula:

=SUMIFS(A1:A9, B1:B9,"=A*", C1:C9,1)

The order of arguments differ between the SUMIFS and SUMIF functions. In particular, the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and editing these similar functions, make sure you put the arguments in the correct order.

Syntax

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

The SUMIFS function syntax has the following arguments .

sum_range  Required. One or more cells to sum, including numbers or names, ranges, or that contain numbers. Blank and text values are ignored.

criteria_range1  Required. The first range in which to evaluate the associated criteria

criteria1  Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32."

criteria_range2, criteria2, …  Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

You can use the wildcard characters — the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Example:

Adds the total number of products sold that begin with "A" and that were sold by Salesperson 1.

Type the data as shown picture below

Step 1:

Step 2:

Step 3:

Step 4:

Step 5:

Step 6:

Step 7: