To use SUMIF in Excel for values greater than a number, put the comparison in quotation marks as the criteria. For example, with values in A2:A6 and amounts to add in B2:B6, use =SUMIF(A2:A6,">100",B2:B6) to add the amounts whose corresponding A value is greater than 100.
What “greater than” means in SUMIF
SUMIF tests each cell in a criteria range against one condition, then adds the matching value from a sum range. The greater-than operator is the symbol >. It excludes a value equal to the threshold: if the condition is greater than 100, a cell containing 100 does not qualify. Use >= when the threshold itself should qualify.
This distinction matters in everyday tasks such as totaling sales above a target, adding expenses over a limit, or summing quantities above a reorder point. The comparison applies to the criteria range; the values being added can be in another column. Each row’s criteria value is paired with the value in the same position in the sum range.
For example, if A2:A6 contains 75, 100, 125, 180, and 240, and B2:B6 contains 12, 20, 15, 30, and 25, the condition greater than 100 matches the last three rows. SUMIF adds 15, 30, and 25, returning 70. The row with 100 is excluded.
SUMIF syntax and where to put the condition
SUMIF has three arguments: SUMIF(range, criteria, [sum_range]). The square brackets in the function description indicate that sum_range is optional; do not type the brackets into a formula. The first argument is the cells Excel checks, the second is the condition, and the optional third argument identifies the cells to add.
- range: the cells tested against the greater-than condition.
- criteria: a number, text, cell reference, or comparison expression that determines which rows match.
- sum_range: the corresponding values to add. If omitted, Excel adds the cells in range that meet the condition.
With the example values in A2:A6 and amounts in B2:B6, this formula checks A and sums B: =SUMIF(A2:A6,">100",B2:B6). It returns 70. Notice that the operator and number are together inside quotation marks. Excel needs the comparison operator to be part of the criteria expression, rather than a separate argument.
If the values to test are also the values to add, with numbers in A2:A6, the shorter formula =SUMIF(A2:A6,">100") returns 545: 125 + 180 + 240. This form is useful when there is only one relevant column.
Greater than versus greater than or equal to
Choose the operator according to whether a value exactly at the boundary should count. For strictly greater values, use >; for values at or above the boundary, use >=. This is not just a formatting distinction: it changes which rows are included.
With values in A2:A6 and amounts in B2:B6 as above, this version includes the row whose A value equals 100: =SUMIF(A2:A6,">=100",B2:B6). Its result is 90, because it adds 20, 15, 30, and 25. If you accidentally use greater than or equal to when the requirement is strictly greater than, the total will include the boundary row.
Other comparison operators can be used in SUMIF criteria, including less than (<), less than or equal to (<=), and not equal to (<>). The same rule applies: when an operator is combined with a value in a literal criterion, put the complete expression in quotation marks.
Use a cell reference for a changeable threshold
A fixed threshold such as 100 is convenient for a one-off total. If the threshold may change, enter it in a worksheet cell and join the operator to that reference with the ampersand (&). This lets someone change the cutoff without editing the formula.
Assume test values are in A2:A6, amounts are in B2:B6, and the chosen threshold is in D2. Use =SUMIF(A2:A6,">"&D2,B2:B6). If D2 contains 100, the formula returns 70; if D2 is changed to 150, it returns 55, from the rows with 180 and 240. Keep the operator inside the quoted text and concatenate the cell reference after it.
This approach also makes a report easier to maintain: label D1 as Threshold, enter the desired value in D2, and refer to D2 in the formula. If the formula is copied elsewhere, use an absolute reference such as $D$2 when the threshold should remain fixed. The corresponding formula is =SUMIF(A2:A6,">"&$D$2,B2:B6); it still returns 70 when D2 is 100. The dollar signs lock the reference during copying, not the value in the cell.
Greater-than criteria for dates
Excel stores real dates as serial numbers, so a greater-than condition can compare dates chronologically. A reliable way to create a date criterion is to concatenate the operator with the DATE function. This avoids ambiguity between regional date formats, where a typed date such as 3/4/2025 might be interpreted differently.
Assume dates are in A2:A4 and amounts are in B2:B4: A2 is January 1, 2025, A3 is January 2, 2025, and A4 is January 5, 2025; B2:B4 contains 10, 25, and 40. Use =SUMIF(A2:A4,">"&DATE(2025,1,1),B2:B4). It returns 65, adding the amounts dated after January 1 and excluding January 1 itself. To include that date, change the operator to >=.
The cells in the date range need to contain actual Excel dates, not text that merely looks like a date. If a date criterion unexpectedly matches nothing, check the underlying cell values and their number formats. Changing the format alone does not convert text into a real date.
When SUMIFS is the better choice
SUMIF handles one condition. Use SUMIFS when a total must satisfy two or more conditions at once, such as a numeric value being greater than a threshold and a category being a particular region. SUMIFS places the sum range first, followed by pairs of criteria ranges and criteria. That argument order differs from SUMIF.
Assume test values are in A2:A6, amounts in B2:B6, and regions in C2:C6. If C2:C6 contains East, West, East, East, West, respectively, and D2 contains 100, this formula sums amounts where the value is greater than D2 and the region is East: =SUMIFS(B2:B6,A2:A6,">"&D2,C2:C6,"East"). It returns 45, adding 15 and 30. Both conditions must be true for a row to contribute to the total.
For exactly one condition, SUMIF is usually the clearer choice. SUMIFS is not merely a longer spelling of the same syntax: using SUMIFS with only one condition is valid, but remember that its first argument is the sum range. If you switch between the functions, check the argument order before copying or adapting a formula.
Common problems and how to fix them
- The operator is not in quotation marks. A criterion such as
">100"is a single comparison expression. If it is entered incorrectly, Excel may treat it as text or reject the formula. - The formula uses the wrong range. The criteria range determines which rows pass the test; the sum range supplies the values to add. Check that both ranges begin and end on matching rows.
- Numbers are stored as text. Values that look numeric may not behave like numbers in comparisons. Check how they were imported, and convert text values to numbers if needed. Number formatting by itself does not always change the underlying value.
- The boundary is included or excluded unexpectedly. Confirm whether the requirement means greater than or greater than or equal to. A value equal to the threshold is the quickest boundary case to inspect.
- The result is zero or unexpectedly small. Check for extra spaces or text in the criteria values, verify that dates are real dates, and make sure the threshold reference points to the intended cell.
- The ranges have different sizes. Keep the criteria range and sum range aligned. For example, do not test A2:A20 while summing B2:B19; mismatched ranges can produce incorrect or confusing results.
Also consider whether errors in the source data need attention. SUMIF does not replace data cleaning: an error value in a relevant range can affect the formula’s result. If a workbook contains imported or inconsistent records, inspect a few matching and nonmatching rows rather than assuming the formula alone is at fault.
Entering and checking the formula in Excel
In Excel for Microsoft 365 on Windows, select the result cell, type the formula beginning with an equals sign, and press Enter. You can also select the Insert Function button beside the formula bar to explore function arguments. On Mac, the formula syntax is the same; keyboard shortcuts and some interface details differ, but there is no separate SUMIF formula for Mac. In Excel for the web, the same SUMIF syntax is supported.
After entering the formula, check it against a small set of rows manually. For a greater-than condition, identify the threshold, mark each row whose test value exceeds it, and add only the corresponding sum values. This is especially useful when results seem plausible but the distinction between greater than and greater than or equal to is important.
Excel installations can use different regional list separators. The formulas here use commas, as in the standard syntax requested; if Excel reports a problem with argument separators on a particular system, that installation may require semicolons. The comparison itself remains the same.
Frequently Asked Questions
How do I write “greater than” in a SUMIF formula?
Put the greater-than operator and threshold together in the criteria argument, such as ">100". For example, with values in A2:A6 and amounts in B2:B6, use =SUMIF(A2:A6,">100",B2:B6).
Does SUMIF greater than include the number itself?
No. A condition of greater than 100 excludes 100. Use greater than or equal to, written ">=100", if 100 should be included.
How do I use a cell value as the SUMIF threshold?
Join the operator to the cell reference with an ampersand. With test values in A2:A6, amounts in B2:B6, and the threshold in D2, use =SUMIF(A2:A6,">"&D2,B2:B6).
Can SUMIF add values greater than a date?
Yes, if the criteria range contains real Excel dates. Use a criterion such as ">"&DATE(2025,1,1) to match dates after January 1, 2025, and provide the corresponding amounts as the sum range.
What is the difference between SUMIF and SUMIFS?
SUMIF applies one condition, while SUMIFS can apply multiple conditions. SUMIF lists the criteria range before its criterion and optional sum range; SUMIFS starts with the sum range, then takes criteria-range and criterion pairs.
Related
- Excel Data Modeling: How It Works and When to Use It
- Excel Data Modelling: Tables, Relationships and Pivots
- Excel Get Data From a Semantic Model: Connect and Analyze
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Between Two Excel Workbooks: A Simple GuideYes. In Excel for Microsoft 365 on Windows, VLOOKUP can search a table in another workbook by using…
- Excel DATE Code: Formula, Examples, and Format CodesExcel’s date code is the DATE function, which creates a real date from a year, month, and day.…
- Excel Letter to Number: Convert Column Labels in FormulasTo convert an Excel column letter to its number, place the label in A2 and enter =COLUMN(INDIRECT(UPPER(A2)&"1")) in…
- Excel Formula Bar: What It Shows and How to Use ItIn Excel, the Formula Bar is the field above the worksheet that shows the contents of the selected…
- Excel Function to Remove Text from a StringExcel does not have one universal “remove text” function: use SUBSTITUTE to remove specified characters or words, REPLACE…
- Insert a Row in Excel and Keep FormulasTo insert a row in Excel and keep formulas, select the row heading where the new row should…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.