Cellwise Start free

Excel guide · 4 min read

How to sum values greater than a number in Excel

Quick answerUse =SUMIF(D2:D9,">1000") to add every amount over 1000. To use a threshold from a cell, join the operator to the cell with &: =SUMIF(D2:D9,">"&H1). Use ">=" to include the threshold itself, "<" for less than, and add a third argument to test one column and sum another: =SUMIF(E2:E9,">30",D2:D9).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGH
1InvoiceCustomerInvoice dateAmountDays lateThreshold1000
2INV-1041Harbor CafeSep 8, 20268500Cutoff dateOct 1, 2026
3INV-1042Northwind LtdSep 15, 2026240045
4INV-1043Bluebell FloristSep 22, 2026100012
5INV-1044Harbor CafeOct 1, 2026145031
6INV-1045Keystone DentalOct 6, 202632000
7INV-1046Northwind LtdOct 13, 2026-2000
8INV-1047Bluebell FloristOct 20, 20266408
9INV-1048Keystone DentalOct 27, 2026187535

Download this sheet as an Excel practice file (.xlsx)

Sum values greater than a number

The sheet is a small business's invoice list: invoice number, customer, date, amount and how many days the payment is late. To total the large invoices, give SUMIF the amount column and the condition. The operator and the number go inside one pair of quotes.

With only two arguments, SUMIF tests and adds the same column. That is what you want when the condition is about the amounts themselves.

=SUMIF(D2:D9,">1000")

Result: 7475

7,475: the invoices of 2,400, 3,200 and 1,875. The 1,000 invoice is not greater than 1,000, so it is left out.

Greater than a value in a cell: ">"&H1

Hard-coding 1000 means editing the formula every time the threshold changes. Put the threshold in H1 and build the condition with &. The quotes hold only the operator; the & glues the cell's value onto it, so Excel sees ">1000".

The common mistake is ">H1" in one piece. Inside quotes, H1 is just two letters, no amount is greater than the text "H1", and the result is 0.

=SUMIF(D2:D9,">"&H1)

Result: 7475

7,475, the same as before. Change H1 to 2000 and it becomes 5,600.

Greater than or equal to, and less than

">=" includes the threshold. Here it adds the Bluebell Florist invoice of exactly 1,000, which ">" left out. Decide which one your rule means before you build the report: "orders of 1,000 or more" is ">=", "orders over 1,000" is ">".

For less than, use "<" or "<=": =SUMIF(D2:D9,"<"&H1) returns 1,290 (850 + 640 and the -200 credit note). Negative values are less than everything, so credit notes and refunds land in a "less than" total. To total only the credits, use =SUMIF(D2:D9,"<0"), which returns -200.

=SUMIF(D2:D9,">="&H1)

Result: 8475

8,475: the three large invoices plus the 1,000 invoice.

Test one column, add another

To total the money tied up in invoices more than 30 days late, test the Days late column and add the Amount column. The third argument is the range to add. It must line up row for row with the range you test, so E2:E9 goes with D2:D9.

For two or more conditions, switch to SUMIFS, which puts the range to add first: =SUMIFS(D2:D9,E2:E9,">30",C2:C9,">="&H2) is the late money from October invoices only.

=SUMIF(E2:E9,">30",D2:D9)

Result: 4275

4,275: Northwind's 2,400 and Keystone's 1,875. Harbor Cafe is 31 days late too, but its amount is text; see below.

A date as the threshold

Dates are numbers in Excel, so "greater than a date" works the same way. With the cutoff date in H2, ">="&H2 adds every invoice dated on or after it. If you write the date inside the formula, build it with DATE so it means the same thing in every country: =SUMIF(C2:C9,">="&DATE(2026,10,1),D2:D9).

Avoid typing ">=10/1/2026" into the condition. Excel reads it using the computer's regional settings, so it is October 1 in the US and January 10 in much of Europe. For a rolling window, use TODAY: =SUMIF(C2:C9,">="&TODAY()-30,D2:D9) adds the last 30 days. For a start and an end date, see the SUMIFS date range guide.

=SUMIF(C2:C9,">="&H2,D2:D9)

Result: 5515

5,515: the October invoices, including the -200 credit note. Harbor Cafe's 1,450 is missing again.

The gotcha: amounts stored as text are skipped

Harbor Cafe's 1,450 invoice in D5 was imported as text. SUMIF ignores text in the range it adds, so it is missing from the "over 1,000", "over 30 days late" and "October" totals, with no error. Signs: the number sits on the left of the cell, there may be a green triangle, and =ISTEXT(D5) returns TRUE.

The real fix is to convert the column once: select it and use Data > Text to Columns > Finish, or the warning icon's Convert to Number. If you cannot change the data, SUMPRODUCT can convert as it goes. The double minus turns text numbers into real numbers, both for the test and for the amounts. It fails with #VALUE! if the column contains words such as "TBC".

=SUMPRODUCT((--D2:D9>H1)*(--D2:D9))

Result: 8925

8,925: the true total over 1,000, now including Harbor Cafe's 1,450.

Which method to pick

One condition: SUMIF, with the threshold in a cell joined by &. A range between two numbers, or a second condition on another column: SUMIFS, for example =SUMIFS(D2:D9,D2:D9,">=500",D2:D9,"<1000") returns 1,490. Data you cannot clean, with numbers stored as text: SUMPRODUCT with --.

Whatever you use, put the threshold in a labelled cell. It makes the rule visible to the next person and lets you test "what if it were 2,000?" in one keystroke.

Common mistakes

Writing ">H1" instead of ">"&H1

Only the operator goes in quotes. Join the cell with &, or SUMIF compares with the text "H1" and returns 0.

Using ">" when the rule means "or more"

">1000" leaves out an amount of exactly 1,000. Use ">=" when the threshold itself should count.

Typing a date as text in the condition

">=10/1/2026" is read differently in different regions. Use ">="&DATE(2026,10,1) or a date in a cell.

Ranges that do not line up

The test range and the sum range must start on the same row. E2:E9 with D1:D8 adds the amount from the row above each match, without any error.

FAQ

How do I sum if greater than 0 in Excel?

Use =SUMIF(D2:D9,">0"). It adds only the positive values and leaves out zeros, credits and refunds.

How do I sum values greater than a cell value?

Join the operator to the cell: =SUMIF(D2:D9,">"&H1). Use ">="&H1 to include the value in H1.

How do I sum between two numbers?

Use SUMIFS with the same column twice: =SUMIFS(D2:D9,D2:D9,">=500",D2:D9,"<1000"). That adds amounts from 500 up to, but not including, 1,000.

Why does my SUMIF greater than return 0?

Usually the cell reference is inside the quotes (">H1"), the amounts are stored as text, or nothing is above the threshold. Check with =COUNTIF(D2:D9,">"&H1) to see how many rows match.

Can I sum values greater than the average?

Yes: =SUMIF(D2:D9,">"&AVERAGE(D2:D9)). Any formula can follow the &, including MAX, TODAY or another cell's calculation.

Functions used

Related guides

Updated