The SUMIFS function in excel is used to sum cells in a range that meet one or more conditions referred to as criteria. The criteria or conditions could be text strings, numbers or dates. In addition to this, we can set logical tests as criteria using logical operators such as (>, <, <>, =, >=, <=) and we could even use wildcards (*?) for partial matching.
In today’s post, we would take you through all the tips and tricks regarding the use of the SUMIFS function and how we can apply this function to real life modeling scenarios. Let’s begin with the Syntax & Arguments for the SUMIFS Function.
Syntax & Argument
The Syntax of the SUMIFS function depends on the number of criteria you set, however the first criteria range is dedicated to the sum range (range of cells you need to sum). The generic syntax for the SUMIFS function is as follows:
=SUMIFS(sum_range, range1, criteria1, range2, criteria2) // 2 conditions
=SUMIFS(sum_range, range1, criteria1, range2, criteria2, range3, criteria3) // 3 conditions
- The Sum_range as discussed in the introduction to this section, is the range containing the numbers you are looking to add up (that is assuming all the criteria are met).
- Criteria 1 is the first condition that must be met for excel to consider corresponding adjacent cells in your Sum_range.
- Range1 is the range excel would check to ensure that the first condition (criteria 1) is met
The range and criteria pair could go up to a maximum of 127 pairs. You should however note that all ranges (including Sum_range) must have the same height i.e Start and end row, unless the function returns a #VALUE error. Let’s see some other things to note when using this function.
Usage notes
- All ranges must have the same size else the function returns a #VALUE error
- To be included in the final result, all conditions must be met
- Criteria can include logical operators such as (>, <, <>, <=, >=)
- A separate range is required for each new condition, and you have a maximum pair of 127 ranges and criteria to exhaust.
Defining the Criteria
The criteria for the SUMIFS function can be specified with the help of logical operators, and in this section we would show you how you can combine these logical operators with text, hardcoded values or values in another cell.
Objective | Criteria |
---|---|
Cells greater than 50 | “>50” |
Cells less than 100 | “<100” |
Cells equal to 75 | 75 or “75” |
Cells greater than or equal to 200 | “>=200” |
Cells less than or equal to 150 | “<=150” |
Cells equal to Blue | “Blue” |
Cells not equal to Green | “<>”red |
Cells that are blank | “” |
Cells that are not blank | “<>” |
Cells that begin with T | “T*” |
Cells greater than B5 | “>”&B5 |
Cells Less than Now | “<“&NOW() |
Notice that the last 2 criteria in the table above deploy concatenation with the use of an ampersand. This is because they refer to another cell. The ampersand instructs excel to evaluate whatever formula or value to the right of it and then join with the logical operator for a final evaluation. So in essence, when a criteria includes a value from another cell, logical operators such as “>” or a formula to be evaluated, it must be joined with a concatenation such as “&”
SUMIFS Function Examples
To reiterate the previous section, the SUMIFS function allows for logical operators and wildcards for partial matching. You can do the following to specify your criteria:
- Hardcode a text as criteria – “Blue“
- Enclose a logical operator in double quotations (“<>“)
- Join a logical operator to a cell or formula by using the concatenation (“&“) operator – (“>”&B5)
- Select a cell as criteria – A4
- Use the asterisk (*) or question mark (?) characters to specify a wildcard SUMIFS – (*IFRS*)
let’s see some various variants of the SUMIFS function below:
Hardcoded Criteria
Specify your criteria with a hardcoded text contained within the formula
=SUMIFS(A1:A10, B1:B10,”British”,C1:C10,”male”) // british & male conditions
Blank Cells
You could use this if you have a range you would like to apply a blank or non-blank condition to. For example, let’s assume our range B1:B10 has cells that are either black or non-blank, we could decide to sum cells in range A1:A10 based on either condition (blank or non-blank).
=SUMIFS(A1:A10, B1:B10, “<>”) // non-blank cells condition in range B1:B10
Logical Operator & Numerical Value or cell reference
This is for when you need to set criteria of a logical operator (<,>) and a number or cell reference. For example, “>5”, “>”&H5.
=SUMIFS(A1:A10, B1:B10, “>”&H5) // condition > value in cell H5
Wildcards
Let’s assume we are preparing a consolidated Opex report and there are a bunch of IFRS adjustments GL codes, and we want to consolidate into one line, we can use the SUMIFS function with wildcards to add up all IFRS adjustments.
Practical use of SUMIFS in Financial Modeling
In our COUNTIFS function page, we stated an example of a financial modeling exercise for the IFRS16 leases for a telecommunications company. We were required to prepare a summary page showing the monthly count and value of lease payments. We touched on how to use the COUNTIFS Function to achieve the count of leases, and in this section, we would touch on how to use the SUMIFS function in excel to return value of lease payments.

Our task is to get the annual lease payment for new leases due in each month. All we need to do is to set the “New lease” as our first criteria for the category range, then set the month in our summary page as our second criteria for the commencement month range.

Explaining the Cell references
- ‘Input Sheet’!$G$5:$G$54: This is the range that contains the annual payment amount we need to add up
- ‘Input Sheet’!$D$5:$D$54: This is our first Criteria range which is the category (Renewal / New Lease).
- “New Lease”: Our first criteria, this is the first hurdle to pass to be included in the final result
- ‘Input Sheet’!$E$5:$E$54: This is our second criteria range which is the commencement date of the lease.
- ‘Summary Sheet’!C$2: Second criteria is the month on our summary sheet.
Result:
If executed correctly, we should get the following annual lease payment amount for each month
