# How to sum values greater than a number in Excel

Use SUMIF with the operator in quotes joined to the cell that holds the number: `=SUMIF(B2:B9,">"&E1)`.

**Excel:** `=SUMIF(B2:B9,">"&E1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Invoice | Amount |  | Over | 1000 |
| INV-201 | 845 |  | Total |  |
| INV-202 | 1260 |  |  |  |
| INV-203 | 1000 |  |  |  |
| INV-204 | 2315 |  |  |  |
| INV-205 | 430 |  |  |  |
| INV-206 | 1785 |  |  |  |
| INV-207 | 990 |  |  |  |
| INV-208 | 3120 |  |  |  |

Formula in E2: `=SUMIF(B2:B9,">"&E1)`

Result: $8,480

## How it works

SUMIF's condition is text, so `">"&E1` builds the condition `">1000"` from the cell. With only two arguments, SUMIF adds the same cells it tests. The 1,000 invoice is left out because it isn't greater than 1,000.

## Other ways

- `=SUMPRODUCT((B2:B9>E1)*B2:B9)`: Writes the comparison directly, which helps when you need a condition SUMIF can't express.
- `=SUMIF(B2:B9,">="&E1)`: Use >= when an amount equal to the threshold should count. (Includes the 1,000 invoice: 9,480.)

## Common mistakes

- `=SUMIF(B2:B9,">E1")` compares with the text "E1" and returns 0. The cell reference must be outside the quotes.
- To test one column and add another, give SUMIF the third argument: `=SUMIF(C2:C9,">"&E1,B2:B9)`.

## Questions

**How do I sum values less than a number?**

Swap the operator: `=SUMIF(B2:B9,"<"&E1)`.

**How do I sum values greater than a number with another condition?**

Use SUMIFS: `=SUMIFS(B2:B9,B2:B9,">"&E1,A2:A9,"INV-2*")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-if-greater-than