Analyzing your business’ performance is a daunting task, evaluating it with key multiple criteria gives you a more detailed analysis. However, using a simple SUM function and even SUMIF function in excel have also reached their limits. SUMIFS function in excel is a function to sum cells that meet multiple criteria, learn more with SUMIFS examples in excel to better understand your business can exceed any limits.

The fundamentals of SUMIFS function in Excel

The SUMIFS function in excel is not at all different from its other similar functions. Similar to the SUMIF function, it is also categorized under Excel Math and Trigonometry functions. Also, the criteria for the SUMIFS function in excel can be based on dates, numbers, text, and even cell reference. Aside from logical operators, such as >,<,=, it works with “wildcards”. But the main difference between the two and what set SUMIFS function in excel apart from the SUMIF is its ability for users to set multiple criteria.

The formula for the SUMIFS function in excel is simply an expanded version of the SUMIF function.

The formula may seem a bit complex, where:

• Sum_range = Range of the cells to add
• Criteria_range1 = Range of cells that Criteria1 is to be applied
• Criteria1 = Determining factor for which cells are to be added. This criteria is only applied to criteria_range1.
• Criteria_range2, criteria2, … =  The additional ranges along with their respective criteria.

Take note that the order of the argument in a SUMIFS function in excel is different from a SUMIF function. Sum_range is the first argument in SUMIFS function while it is the third argument in the SUMIF function.

Business applications of SUMIFS function in excel

SUMIFS function in excel can be applied to the same set of criteria used in a SUMIF function:

• Any numeric value such as an integer (100 or -100), decimal (.001), date (01/01/2019), time (9:00:00 AM) or logical value (TRUE/FALSE).
  • A text string such as “Sales”, “Customer Name”, “ProductID” or
  • An expression such as “>100”, “<>0”).

But the SUMIFS function in excel can use multiple criteria in one single formula. Below are business-related SUMIFS examples in excel.

SUMIFS Example in Excel Using a TWO Text Criteria

An entrepreneur engaged in the coffee business has the following financial data. Using the SUMIFS function in excel he can customize the data to evaluate his day to day business operations. Using the SUMIFS example in excel below, the entrepreneur’s criteria for his Total Cost were the following:

Criteria 1: Product Robusta Coffee
Criteria 2: Supplier from Brazil

SUMIFS Function in Excel Using Greater than Criteria And a Text Criteria

The entrepreneur can also the SUMIFS function in excel to identify the Total Cost based on the product type and a specific unit purchased. The SUMIFS example in excel is illustrated below.

Criteria 1: Product Robusta Coffee
Criteria 2: Units bought of greater than 1500 units

A similar SUMIFS example in excel with a different set of criteria.

Criteria 1: Product Arabica Coffee
Criteria 2: Units bought of less than 1500 units

SUMIFS Example in Excel Using Greater than Criteria, Text criteria and Date criteria

The beauty of using SUMIFS function in excel is you can narrow down your business analysis. The SUMIFS example in excel below has 3 criteria for the Total Cost.

Criteria 1: Product Robusta Coffee
Criteria 2: Units bought of greater than 1500 units
Criteria 3: Delivered before July 2018

Key points on SUMIF vs. SUMIFS function in excel

• The SUMIF and SUMIFS function in excel are quite similar. The main difference is the number of criteria you can use to conduct a detailed analysis, especially when dealing with a large amount of financial data. When using SUMIF, you can evaluate only one criterion whereas the SUMIFS function in Excel allows you to use multiple criteria.
• The SUMIF function can be only used for a single continuous range (one-column) based on a single defined range with a single condition (one-criteria). The SUMIFS function in excel can be applied in multiple continuous ranges.
• As shown in the SUMIFS example in excel it is important to remember the order of the argument in a SUMIFS function in excel is different from a SUMIF function, Sum_range is the first argument in SUMIFS function.