# How to sum values based on criteria in Excel (SUMIF)

Use =SUMIF(criteria_range, criteria, sum_range): it adds the numbers in sum_range on the rows where criteria_range matches.

**Excel:** `=SUMIF(B2:B11,G1,D2:D11)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Date | Region | Product | Sales |  | Region | West |
| 2026-01-05 | West | Laptop | 1840 |  | Product | Monitor |
| 2026-01-11 | East | Monitor | 420 |  | Total |  |
| 2026-01-19 | West | Monitor | 395 |  |  |  |
| 2026-02-02 | North | Laptop | 1720 |  |  |  |
| 2026-02-09 | West | Dock | 210 |  |  |  |
| 2026-02-16 | East | Laptop | 1955 |  |  |  |
| 2026-02-23 | West | Monitor | 610 |  |  |  |
| 2026-03-03 | South | Monitor | 380 |  |  |  |
| 2026-03-10 | West | Laptop | 2100 |  |  |  |
| 2026-03-17 | East | Dock | 185 |  |  |  |

Formula in G3: `=SUMIF(B2:B11,G1,D2:D11)`

Result: $5,155

## How it works

SUMIF looks down `B2:B11` for rows equal to the region in `G1`, and adds the sales in `D2:D11` on those rows. The criteria range and the sum range line up row by row, so they should start on the same row and be the same size.

## Other ways

- `=SUMIFS(D2:D11,B2:B11,G1)`: SUMIFS puts the sum range first and takes more conditions later, so it's the better habit.
- `=SUMPRODUCT((B2:B11=G1)*D2:D11)`: Works when the condition needs a calculation, such as a test on part of the text.

## Common mistakes

- The argument order differs: SUMIF is (range, criteria, sum_range) and SUMIFS is (sum_range, range, criteria). Swapping them returns 0 or the wrong total.
- Text criteria are not case-sensitive, and extra spaces matter: "West " does not match "West".
- Numbers stored as text are not added. If the total looks too small, check for left-aligned numbers.

## Questions

**How do I sum with a partial match?**

Use wildcards: `=SUMIF(C2:C11,"*Lap*",D2:D11)` adds sales for every product containing "Lap".

**How do I sum values that are not equal to something?**

Use "<>": `=SUMIF(B2:B11,"<>West",D2:D11)` adds every region except West.

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

Source: https://spreadsheetpractice.com/formulas/sumif-sum-based-on-criteria