background
Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for May 2024 for May 2024
:
Private EquityReal Estate Investing
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for May 2024 is Open
Wall Street Prep

SUMIFS Function

Step-by-Step Guide to Understanding the SUMIFS Function in Excel

Last Updated July 4, 2023

Learn Online Now

SUMIFS Function

How to Use SUMIFS Function in Excel?

The Excel SUMIFS function is a built-in feature that adds the value of cells within a range based on a given set of conditions.

The specified criteria consist of logical conditions containing numeric values, dates, or text.

Given those conditions, the SUMIFS function determines which specific cells within the selected range meet the set of conditions using a “TRUE” or “FALSE” logic.

If the user-specified criteria are met, the cell is included in the calculation of determining the sum; otherwise, it is ignored.

For example, one use-case of the SUMIFS function would be to calculate the total amount of revenue contributed by a customer that is located in a certain state. By segmenting customers by states and understanding the concentration, company management can better grasp where their product is most receptive geographically and in which specific areas their current strategy might need improvements.

SUMIF vs. SUMIFS Function: What is the Difference?

Simply put, the SUMIFS function is an extension of the SUMIF function, with more capabilities.

In particular, the SUMIFS function in Excel provides the user with the option to select multiple criteria.

While the two functions are near identical with respect to the user’s intent—i.e. to calculate the sum of values in cells that meet a provided criterion—the SUMIFS function is not constrained to only one condition like SUMIF.

Thus, SUMIFS is a more practical function if the task at hand involves a criterion with multiple conditions, rather than only one.

Excel SUMIFS Function Formula

The Excel SUMIFS function formula is as follows.

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] …)
  • sum_range” → The range in which the criteria is applied and the value of the cells within the range are added if the conditions are met.
  • criterion_range1” → The first condition that the function applies to the selected cell range.
  • sum_range2” and “criteria2” → The second range and the corresponding criteria in which the function is applied.

The brackets around the “sum_range2” and “criteria2” input signify that after the first range and criteria pair, the rest are optional and can be omitted (i.e. left blank).

Excel SUMIFS Syntax: Logical Operators and Wildcards

SUMIFS function can support logical operators and wildcards in the criterion.

In order for the function to work as intended, the proper syntax must be used.

Generally, most operators must be enclosed in double quotes (””) using the proper syntax.

There are exceptions, however, such as purely numeric values or binary conditions.

  • Cell Reference: For example, a criterion containing a cell reference must have the operator enclosed in parentheses and concatenated, e.g. “>”&B1. But that would not be necessary if it were binary logic, e.g. =B1.
  • Dates: In addition, a criterion based on a date must also have the operator in parentheses and concatenated, e.g. “<“&TODAY().
Criterion Description
=
  • Cells Equal To
<>
  • Cells NOT Equal To
>
  • Cells Greater Than
<
  • Cells Less Than
>=
  • Cells Greater Than OR Equal To
<=
  • Cells Less Than OR Equal To
“”
  • Blank
“<>”
  • Not Blank

The use of wildcard characters refer to the criterion containing question marks, asterisks, and tildes — the chart below describes each briefly.

Wildcards Description
?
  • Question marks match a single character in a specific position.
*
  • Asterisks match any number of characters.
    • Cells that Begin With → “N*
    • Cells that End With → “*N”
    • Cells with Partial Value → “*N*
~
  • Tildes match a question mark or asterisk placed before the character (e.g. ◦ “~?”, ◦ “~*”)

SUMIFS Function Calculator – Excel Template

We’ll now move on to a modeling exercise, which you can access by filling out the form below.

dl
Submitting...

SUMIFS Function Revenue Calculation Example

Suppose we’re tasked with segmenting a company’s annual revenue in the trailing twelve months based on two conditions.

  • Category: Large Enterprise or SME
  • State: New York, Massachusetts, Texas, Florida

The company generated a total of $8 million in revenue with ten customers.

The data set below contains the revenue per customer, customer category, and the state in which the customer is located.

Customer Category State Revenue
A Large Enterprise Pennsylvania $2,000,000
B SME Texas 700,000
C SME Florida 400,000
D Large Enterprise Massachusetts 2,800,000
E Large Enterprise New York 2,500,000
F SME Texas 400,000
G Large Enterprise New York 2,700,000
H SME Texas 500,000
I SME Florida 400,000
J Large Enterprise Massachusetts 2,600,000
Total Revenue $15,000,000

Using the SUMIFS function, the first range we select is the “Category” column, followed by the customer type criteria. Here, we’ve entered the text in a separate section, so we can link to it directly.

The second range we’ll select is the “State” column, along with the cell reference to the state criteria.

For example, the SUMIFS formula we’ll enter into Excel for the “Large Enterprise” and “New York” pair is as follows.

=SUMIFS($E$5:$E$14,$C$5:$C$14,G6,$D$5:$D$14,H6)
Excel SUMIFS Function Formula Syntax

Since we’ll repeat this process for all other pairs, we’ll anchor the range selections by clicking F4 to format it as an absolute reference.

Once done, we arrive at the following revenue distribution data points:

  • Large Enterprise + New York → $5,200,000
  • Large Enterprise + Massachusetts → $5,400,000
  • Large Enterprise + Pennsylvania → $2,000,000
  • SME + Texas → $1,600,000
  • SME + Florida → $800,000

SUMIFS Function in Excel Tutorial

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.
Learn More
Comments
0 Comments
Inline Feedbacks
View all comments
Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.