SUMIFS Larger Than: Excel Formula Guide

Formulas & functions · Updated November 22, 2026

To sum values in Excel where a related cell is larger than a number, use the SUMIFS function with a comparison operator in the criteria, like =SUMIFS(sum_range, criteria_range, ">"&100). For example, to sum sales in B2:B20 where the amount in A2:A20 is greater than 500, the formula is =SUMIFS(B2:B20, A2:A20, ">500").

How SUMIFS Handles “Larger Than”

The SUMIFS function adds numbers in a range that meet one or more conditions. Its syntax is:

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

For a “larger than” condition, you write the operator inside a text string. Excel accepts ">" (greater than), ">=" (greater than or equal to), "<", "<=", and "<>" (not equal to). The operator must be in double quotes, and you can combine it with a cell reference using the ampersand (&) or directly with a number, as in ">500".

Key details:

  • The sum range and criteria range must be the same size, but they do not have to be adjacent.
  • Criteria are case-insensitive and do not match partial text unless you use wildcards.
  • If no cells meet the condition, SUMIFS returns 0.

Using a Cell Reference for the Threshold

Instead of hardcoding the number, you can reference a cell. For instance, with the threshold in D1, write:

=SUMIFS(B2:B20, A2:A20, ">"&D1)

This formula sums B2:B20 where A2:A20 is greater than the value in D1. The ampersand joins the operator with the cell value. If D1 contains 100, the criteria becomes ">100". This makes the formula easy to update without editing the formula itself.

For a "greater than or equal to" condition, use ">="&D1. For "less than", use "<"&D1.

Multiple Criteria: Adding More Conditions

You can add more criteria pairs. For example, to sum sales in B2:B20 where A2:A20 is larger than 100 and C2:C20 equals "East", use:

=SUMIFS(B2:B20, A2:A20, ">100", C2:C20, "East")

All criteria must be true for a row to be included. If you need an OR condition (e.g., larger than 100 OR larger than 200), you cannot use SUMIFS directly; use SUMPRODUCT or add two SUMIF results. For example:

=SUMIF(B2:B20, A2:A20, ">100") + SUMIF(B2:B20, A2:A20, ">200") would double-count values above 200, so instead use =SUMIFS(B2:B20, A2:A20, ">100", A2:A20, "<=200") + SUMIFS(B2:B20, A2:A20, ">200").

Common Errors and How to Fix Them

Several mistakes trip up users:

  • Operator in quotes without ampersand: Writing ">"&100 is correct; writing ">100" also works for a literal number. But if you write ">"100 (missing ampersand), Excel shows a #VALUE! error.
  • Mismatched range sizes: If sum range is B2:B20 and criteria range is A2:A21, Excel returns #VALUE!.
  • Using SUMIF instead of SUMIFS: SUMIF has a different argument order (=SUMIF(criteria_range, criteria, sum_range)). Mixing them up is common.
  • Text-formatted numbers: If the criteria range contains numbers stored as text, the comparison may not work. Select the column and use the error-checking option to convert to numbers.

SUMIFS vs. SUMIF vs. SUMPRODUCT

For a single condition, SUMIF works fine and is slightly faster. For multiple conditions, SUMIFS is the right tool. SUMPRODUCT offers more flexibility, such as summing with OR logic or using arrays. Example of SUMPRODUCT for "larger than 100 OR less than 50":

=SUMPRODUCT(B2:B20 * ((A2:A20>100)+(A2:A20<50)))

This multiplies each value in B by 1 if the condition is true, then sums the results. Note that SUMPRODUCT can handle whole columns (e.g., A:A) without performance issues, unlike SUMIFS which can slow down with full-column references.

Using SUMIFS with Dates and Text

The same logic applies to dates. To sum values where a date is after a certain date, use:

=SUMIFS(B2:B20, A2:A20, ">"&DATE(2025,1,1))

Or reference a cell containing a date. For text, you can use wildcards. To sum where a name starts with "J", use "J*".

Performance and Large Data Sets

When working with thousands of rows, SUMIFS is efficient, but avoid using entire columns (e.g., A:A) if possible. Instead, limit the range to the actual data (e.g., A2:A10000). If you need to sum based on a condition that changes often, consider using a pivot table or Excel's FILTER function (available in Excel 2021 and Microsoft 365) combined with SUM:

=SUM(FILTER(B2:B20, A2:A20>100))

This formula returns the sum of B where A is greater than 100. It's more flexible for complex logic but requires a dynamic array-capable version.

Frequently Asked Questions

Can I use "greater than" with a cell reference without the ampersand?

No. Excel requires the operator to be in quotes and joined with the cell reference using &. Writing ">D1" would treat "D1" as text, not a cell reference.

Why does my SUMIFS return 0 even though there are matching values?

Check that the criteria range and sum range are aligned properly. Also, if the criteria range contains numbers stored as text, the comparison may fail. Convert them to numbers using the Text to Columns feature or by multiplying by 1.

Does SUMIFS work with "greater than or equal to"?

Yes. Use ">=" as the operator. For example, =SUMIFS(B2:B20, A2:A20, ">=100") sums where A is 100 or more.

Can I sum based on a condition in another sheet?

Yes. Reference the other sheet in the criteria range, like =SUMIFS(B2:B20, Sheet2!A2:A20, ">100"). The sum range can be on a different sheet as well.

What if I need to sum values larger than a number but also smaller than another?

Add two criteria pairs. For example, =SUMIFS(B2:B20, A2:A20, ">100", A2:A20, "<200") sums values between 100 and 200 (exclusive).

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.