Sum if Greater Than: Excel SUMIF Guide

This page explains how to sum numbers in a range only when they are greater than a specified value, using the Excel SUMIF function. You enter the range to check, the threshold value, and the range to sum (if different), and the formula returns the total of all matching cells. The core formula is =SUMIF(range, ">"&value, sum_range).

Note: The calculator above uses a simplified list-based approach to demonstrate the logic. In Excel, you would use the SUMIF function directly on a range of cells.

Formula

The SUMIF function in Excel has the syntax:

=SUMIF(range, criteria, [sum_range])

• range: The range of cells you want to apply the criteria against (e.g., A2:A100).
• criteria: The condition that determines which cells to sum, written as text. For “greater than a value”, use ">"&value or ">10" directly.
• sum_range (optional): The actual cells to sum. If omitted, Excel sums the cells in range itself.

The criteria is a text string, so you must enclose operators like >, <, >= in quotation marks. To reference a cell containing the threshold (e.g., B1), use the ampersand to concatenate: ">"&B1.

Worked Example

Suppose you have sales figures in cells A2:A6: 5, 12, 8, 15, 3. You want to sum only the values greater than 10.

1. Set the threshold value: 10.
2. Write the formula: =SUMIF(A2:A6, ">10").
3. Excel checks each cell: 5 (no), 12 (yes), 8 (no), 15 (yes), 3 (no).
4. Sum the qualifying values: 12 + 15 = 27.

The result is 27. If you had a separate sum_range (e.g., quantities in B2:B6), the formula would sum only the corresponding cells in B where the A value exceeds 10.

How to Use SUMIF with Greater Than in Real Spreadsheets

The most common use is summing sales above a target, hours worked over a threshold, or expenses exceeding a budget. Here are practical steps:

1. Identify the range to evaluate (e.g., column A with dates or amounts).
2. Decide the threshold value. Put it in a cell (e.g., D1) so you can change it without editing the formula.
3. Use the formula: =SUMIF(A2:A100, ">"&D1, B2:B100) where B is the column to sum.
4. Press Enter. The result updates automatically when you change the threshold.

For example, if column A lists monthly expenses and column B lists amounts, =SUMIF(A2:A13, ">500", B2:B13) sums all expenses above $500.

Common Mistakes and How to Avoid Them

• Forgetting quotes around the operator: =SUMIF(A1:A10, >10) gives an error. Always use ">10" or ">"&B1.
• Mixing up range and sum_range: The first range is for the criteria, the third is for summing. If they are the same, omit the third argument.
• Using text values when numbers are stored as text: SUMIF ignores text-formatted numbers. Use the VALUE function or convert to numbers.
• Including the threshold itself: “>10” excludes 10. If you want to include it, use “>=10”.

SUMIF vs. SUMIFS vs. SUMPRODUCT

For a single condition, SUMIF is simplest. For multiple conditions (e.g., sum if greater than 10 AND less than 20), use SUMIFS:

=SUMIFS(sum_range, criteria_range1, ">10", criteria_range2, "<20")

SUMPRODUCT offers more flexibility, especially with arrays or when you need to perform calculations before summing. For example, =SUMPRODUCT((A1:A10>10)*(A1:A10)) sums values greater than 10 without a separate sum_range, but it can be slower on huge datasets.

Choose SUMIF for one condition, SUMIFS for multiple conditions, and SUMPRODUCT for complex logic or when you need to avoid helper columns.

Alternatives: Pivot Tables and Filtering

If you prefer a visual approach, you can filter your data and read the subtotal from the status bar. However, that is manual and not dynamic. A Pivot Table can group data and show sums, but it requires refreshing when data changes. The SUMIF formula is the only method that updates instantly when you change the threshold or source data.

Another alternative is using the FILTER function (in Excel 365) combined with SUM: =SUM(FILTER(B2:B100, A2:A100>10)). This is more modern but not available in older Excel versions.

Frequently Asked Questions

How do I sum if greater than a cell value?

Use =SUMIF(A1:A10, ">"&B1) where B1 contains the threshold. The ampersand joins the greater-than operator with the cell reference. This way, changing B1 updates the result.

Can I sum if greater than zero only?

Yes, use =SUMIF(A1:A10, ">0"). This is a common way to ignore negative numbers and sum only positive values in a column.

Why is my SUMIF returning 0 even though there are matching numbers?

Check that the numbers are stored as numbers, not text. Also ensure the criteria is formatted correctly: ">10" not >10. If you use a cell reference, make sure it doesn't contain a trailing space.

Does SUMIF work with dates?

Yes, dates are stored as numbers in Excel. To sum values after a certain date, use =SUMIF(A1:A10, ">"&DATE(2024,1,1), B1:B10) or reference a cell containing a date.

What if I want to sum values greater than a number but less than another?

Use SUMIFS with two criteria: =SUMIFS(sum_range, criteria_range, ">10", criteria_range, "<20"). The same range can be used for both criteria.

Can I use SUMIF with wildcards for text?

Yes, wildcards work with text criteria, but not with numeric operators. For example, =SUMIF(A1:A10, "apple*", B1:B10) sums where the text starts with "apple". For numbers, you must use operators.

Related

• Expose Self-Hosted Service Securely Without Cloudflare Tunnel
• Nano Delivery for Functional Foods: What It Can and Can’t Do
• Disable Apple Watch Turn-by-Turn Vibration While Driving

in threshold | Threshold value | - | 10 | 0 | 100000
in values | Values to check | - | 5, 12, 8, 15, 3 | 0 | 100000
out total | Sum of values greater than threshold | - | sum(list_filtered) | 2