# How to count rows where any column meets a condition in Excel

Add a test per column and count rows where the total is above zero: `=SUMPRODUCT(--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0))`.

**Excel:** `=SUMPRODUCT(--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Student | Test 1 | Test 2 | Test 3 |  | At least | 90 |
| Ava | 88 | 92 | 85 |  | Students |  |
| Ben | 76 | 81 | 79 |  |  |  |
| Chloe | 95 | 97 | 99 |  |  |  |
| Dan | 90 | 72 | 68 |  |  |  |
| Eve | 84 | 89 | 86 |  |  |  |
| Finn | 91 | 93 | 78 |  |  |  |
| Gia | 70 | 65 | 88 |  |  |  |
| Hugo | 82 | 79 | 94 |  |  |  |

Formula in G2: `=SUMPRODUCT(--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0))`

Result: 5

## How it works

Each comparison returns TRUE or FALSE for every student, and adding the three gives how many tests each one scored 90 or more on: 0 to 3. `>0` keeps the students with at least one, so Chloe, with three high scores, counts once.

Five students reached 90 at least once, even though there are eight scores of 90 or more.

## Other ways

- `=COUNTIF(B2:D9,">="&G1)`: Counts scores, not students. (Returns 8: Chloe counts three times and Finn twice.)

## Common mistakes

- COUNTIF on the whole block counts cells. When a row can match more than once, it overcounts rows.
- Forgetting `>0` sums the matches per row, which gives the cell count again.

## Questions

**What about a table with many columns?**

Use a helper column with `=COUNTIF(B2:D2,">="&$G$1)>0` per row and count the TRUEs, or in Excel 365 `=SUM(--BYROW(B2:D9,LAMBDA(r,OR(r>=G1))))`.

**How do I count rows where every column meets the condition?**

Use COUNTIFS with one condition per column: `=COUNTIFS(B2:B9,">="&G1,C2:C9,">="&G1,D2:D9,">="&G1)`.

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/count-rows-where-any-column-meets-a-condition