SUMIF Greater Than or Less Than a Number

To use SUMIF greater than a number, put the operator and the number together in double quotes: =SUMIF(B2:B8,">500") adds every value above 500. Swap the operator for less than, greater than or equal to, or less than or equal to. This guide shows each comparison on one order list, then covers limits in cells, conditions on a different column, ranges and the reasons a total comes out as 0.

How to sum values greater than a number#

SUMIF adds the numbers that meet one condition, called the criteria. Its syntax is:

=SUMIF(range, criteria, [sum_range])

The square brackets mean sum_range is optional. When you leave it out, SUMIF adds the same cells it checks. Every example uses this order list in A1:C8:

To add the orders above 500:

1. Click an empty cell, such as E2.
2. Type =SUMIF(B2:B8,">500").
3. Press Enter.

The result is 2660: 820 + 1200 + 640. Order 1003 is exactly 500, so it is left out. The quotes are required: without them, Excel won't accept the formula. For a full introduction, see how to use SUMIF.

SUMIF less than, greater than or equal to, and more#

Change the operator to change the comparison. On the Amount column, which totals 3875:

The difference between > and >= is the 500 order, which is why the second total is 500 higher. A quick check: "greater than 500" plus "less than or equal to 500" should equal the full total, and 2660 + 1215 = 3875.

Write the operators exactly like this. => and =< are not valid, and neither is !=. For more on leaving a value out, see SUMIF not equal to.

How to use SUMIF greater than a cell value#

Put the limit in a cell so you can change it without editing the formula. With 500 in E1:

=SUMIF(B2:B8,">"&E1)

The result is 2660. The & joins the text ">" to the value in E1, so Excel sees ">500". Change E1 to 1000 and the total becomes 1200.

The common mistake is ">E1". Inside quotes, E1 is just text, so SUMIF looks for amounts greater than the word "E1" and returns 0. The operator goes in quotes, the cell reference stays outside.

How to check one column and add another#

Use the third argument when the condition is on one column but the numbers to add are in another. To total the Amount for orders with 10 or more units:

=SUMIF(C2:C8,">=10",B2:B8)

SUMIF checks the Units column, finds orders 1002 (12 units) and 1004 (20 units), and adds their amounts from column B: 820 + 1200 = 2020. Order 1006 has 9 units, so it is left out even though its amount is large.

Make sure the two ranges are the same size and start on the same row. If the sum range is shifted by a row, SUMIF adds the wrong numbers without any warning.

How to sum between two numbers#

SUMIF takes one condition, so for a range such as 300 to 900 use SUMIFS with two conditions on the same column:

=SUMIFS(B2:B8,B2:B8,">=300",B2:B8,"<=900")

The result is 2335: 820 + 500 + 640 + 375. A value is added only when it passes both tests. SUMIFS puts the sum range first, which is the opposite of SUMIF. The comparison of SUMIF vs SUMIFS explains why.

You can also subtract two SUMIFs: =SUMIF(B2:B8,">=300")-SUMIF(B2:B8,">900") gives 3535 - 1200 = 2335. SUMIFS is easier to read, so prefer it.

Greater than with dates or the average#

The criteria can include a function, joined with &. To add the amounts above the average order:

=SUMIF(B2:B8,">"&AVERAGE(B2:B8))

The average is about 553.6, so this adds 820, 1200 and 640 for 2660. Dates are numbers too, so ">="&DATE(2024,7,1) adds the rows from 1 July 2024 onwards when it is used on a date column with a sum range. Build dates with DATE rather than typing them as text, because text dates depend on regional settings.

Why SUMIF greater than returns 0 or the wrong total#

Check these causes in order:

1. Numbers stored as text. Imported numbers are sometimes text, and SUMIF skips them. Real numbers sit on the right of the cell by default. Convert them with VALUE or Data > Text to Columns.
2. A cell reference inside the quotes. Use ">"&E1.
3. Wrong operator order. Use ">=", not "=>".
4. Missing sum range. If the condition is on Units but you want Amounts added, you need the third argument.
5. Misaligned ranges. The check range and sum range must line up row for row.

To count the matching rows rather than add them, use the same criteria in COUNTIF; the guide to COUNTIF greater than shows how. To add rows by a word inside the text, see SUMIF contains text.

SUMIF greater than with a second condition#

SUMIF takes one condition. To add a second one, switch to SUMIFS. To total the amounts over 500 for orders of fewer than 15 units:

=SUMIFS(B2:B8,B2:B8,">500",C2:C8,"<15")

The result is 1460: order 1002 (820, 12 units) and order 1006 (640, 9 units). Order 1004 is over 500 but has 20 units, so it is left out. Each extra condition is one more range and criteria pair.

Practise it#

Greater-than totals are among the most common SUMIF tasks in job tests. Practise SUMIF in a live spreadsheet: you type the criteria and the lesson checks it instantly. Keep the SUMIF function guide open as a reference, and try the exercise on this page first if you want a quick warm-up.