SUMIFS perform

SUMIFS perform

The SUMIFS perform, one of many math and trig features, provides all of its arguments that meet a number of standards. For instance, you’ll use SUMIFS to sum the variety of retailers within the nation who (1) reside in a single zip code and (2) whose income exceed a particular greenback worth.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

This video is a part of a coaching course referred to as Superior IF features.

Syntax

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

  • =SUMIFS(A2:A9,B2:B9,”=A*”,C2:C9,”Tom”)

  • =SUMIFS(A2:A9,B2:B9,”<>Bananas”,C2:C9,”Tom”)

Argument identify

Description

Sum_range    (required)

The vary of cells to sum.

Criteria_range1    (required)

The vary that’s examined utilizing Criteria1.

Criteria_range1 and Criteria1 arrange a search pair whereby a variety is looked for particular standards. As soon as objects within the vary are discovered, their corresponding values in Sum_range are added.

Criteria1    (required)

The standards that defines which cells in Criteria_range1 might be added. For instance, standards could be entered as 32, “>32”, B4, “apples”, or “32”.

Criteria_range2, criteria2, …    (optionally available)

Further ranges and their related standards. You may enter as much as 127 vary/standards pairs.

Examples

To make use of these examples in Excel, drag to pick the info within the desk, right-click the choice, and choose Copy. In a brand new worksheet, right-click cell A1 and choose Match Vacation spot Formatting underneath Paste Choices.

Amount Offered

Product

Salesperson

5

Apples

Tom

4

Apples

Sarah

15

Artichokes

Tom

3

Artichokes

Sarah

22

Bananas

Tom

12

Bananas

Sarah

10

Carrots

Tom

33

Carrots

Sarah

Formulation

Description

=SUMIFS(A2:A9, B2:B9, “=A*”, C2:C9, “Tom”)

Provides the variety of merchandise that start with A and had been bought by Tom. It makes use of the wildcard character * in Criteria1, “=A*” to search for matching product names in Criterial_range1 B2:B9, and appears for the identify “Tom” in Criterial_range2 C2:C9. It then provides the numbers in Sum_range A2:A9 that meet each circumstances. The result’s 20.

=SUMIFS(A2:A9, B2:B9, “<>Bananas”, C2:C9, “Tom”)

Provides the variety of merchandise that are not bananas and are bought by Tom. It excludes bananas by utilizing <> within the Criteria1, “<>Bananas”, and appears for the identify “Tom” in Criterial_range2 C2:C9. It then provides the numbers in Sum_range A2:A9 that meet each circumstances. The result’s 30.

Widespread Issues

Drawback

Description

0 (Zero) is proven as a substitute of the anticipated outcome.

Be sure that Criteria1,2 are in citation marks in case you are testing for textual content values, like an individual’s identify.

The result’s incorrect when Sum_range has TRUE or FALSE values.

TRUE and FALSE values for Sum_range are evaluated in another way, which can trigger sudden outcomes once they’re added.

Cells in Sum_range that comprise TRUE consider to 1. Those who comprise FALSE consider to 0 (zero).

Finest practices

Do that

Description

Use wildcard characters.

Utilizing wildcard characters just like the query mark (?) and asterisk

in criteria1,2 will help you discover matches which might be related however not precise.

A query mark matches any single character. An asterisk matches any sequence of characters. If you wish to discover an precise query mark or asterisk, sort a tilde (~) in entrance of the query mark.

For instance, =SUMIFS(A2:A9, B2:B9, “=A*”, C2:C9, “To?”) will add all cases with identify that start with “To” and ends with a final letter that would range.

Perceive the distinction between SUMIF and SUMIFS.

The order of arguments differ between SUMIFS and SUMIF. Particularly, the sum_range argument is the primary argument in SUMIFS, however it’s the third argument in SUMIF. It is a widespread supply of issues utilizing these features.

In case you’re copying and modifying these related features, be sure you put the arguments within the appropriate order.

Use the identical variety of rows and columns for vary arguments.

The Criteria_range argument should comprise the identical variety of rows and columns because the Sum_range argument.

High of Web page

Do you will have a particular perform query?

Put up a query within the Excel neighborhood discussion board

Assist us enhance Excel

Do you will have recommendations about how we will enhance the following model of Excel? In that case, please try the matters at Excel Consumer Voice.

See Additionally

See a video on use superior IF features like SUMIFS

The SUMIF perform provides solely the values that meet a single standards

The SUMSQ perform sums a number of values after it performs a mathematical sq. operation on every of them

The COUNTIF perform counts solely the values that meet a single standards

The COUNTIFS perform counts solely the values that meet a number of standards

IFS perform (Workplace 365, Excel 2016 and later)

Overview of formulation in Excel

keep away from damaged formulation

Use error checking to detect errors in formulation

Math & Trig features

Excel features (alphabetical)

Excel features (by Class)

Leave a Reply