# Sum and count with criteria

Use matching ranges and explicit boundaries.

FormulaSmith | 2026-10-08 | EN

English Excel: comma arguments. Regional settings may use another separator.

Import sales.csv at A1 (comma) or paste sales.tsv at A1. Keep the English sample values unchanged.

| ID | Region | Amount | Status |
| --- | --- | --- | --- |
| P101 | North | 120 | Paid |
| P102 | South | 80 | Open |
| P103 | North | 200 | Paid |
| P104 | West | 150 | Open |
| P105 | North | 50 | Open |
| P106 | South | 100 | Paid |

## One condition: North

```excel
=SUMIFS(C2:C7,B2:B7,"North")
```

RESULT: 370

Adds 120 + 200 + 50. Criteria text must match the data.

## North AND Paid

```excel
=SUMIFS(C2:C7,B2:B7,"North",D2:D7,"Paid")
```

RESULT: 320

The 50 marked Open is excluded.

## Count matching rows

```excel
=COUNTIFS(B2:B7,"North",D2:D7,"Paid")
```

RESULT: 2

Counts records, not their amounts.

## At least 100, below 200

```excel
=SUMIFS(C2:C7,C2:C7,">=100",C2:C7,"<200")
```

RESULT: 370

Includes 100, 120 and 150. Excludes 200.

## Threshold from a cell

```excel
=COUNTIFS(C2:C7,">="&C2)
```

RESULT: 3

C2 is 120. Join the operator and the cell using &.

## An ID pattern

```excel
=COUNTIFS(A2:A7,"P10?")
```

RESULT: 6

? matches one character. * matches any sequence; ~ escapes a wildcard.

## BEFORE YOU USE IT

SUMIFS puts the sum range first. COUNTIFS has no sum range. Multiple criteria use AND logic; all ranges must have matching dimensions.

## MICROSOFT REFERENCES

- [SUMIFS](https://support.microsoft.com/en-us/excel/functions/sumifs-function) (2026-10-08)
- [COUNTIFS](https://support.microsoft.com/en-us/excel/functions/countifs-function) (2026-10-08)

https://formulasmith.com/en/cheat-sheets/