How to count rows where any column meets a condition in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0))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)).
- Result on the example
- 5 in
G2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0))Result5
B2:B98 rows × 1 col: 88, 76, 95, 90, …A range: 8 cells in one column.
G190The value in G1.
B2:B9>=G1{8 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …The >= operator tests at least.
C2:C98 rows × 1 col: 92, 81, 97, 72, …A range: 8 cells in one column.
C2:C9>=G1{8 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The >= operator tests at least.
B2:B9>=G1)+(C2:C9>=G1{8 rows × 1 col} 1, 0, 2, 1, …The + operator adds.
D2:D98 rows × 1 col: 85, 79, 99, 68, …A range: 8 cells in one column.
D2:D9>=G1{8 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …The >= operator tests at least.
B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1{8 rows × 1 col} 1, 0, 3, 1, …The + operator adds.
B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0{8 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The > operator tests greater than.
-((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0{8 rows × 1 col} -1, 0, -1, -1, …Makes the value negative.
--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0{8 rows × 1 col} 1, 0, 1, 1, …Makes the value negative.
SUMPRODUCT(--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0{8 rows × 1 col} 1, 0, 1, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0)). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIF(B2:D9,">="&G1) | 8Different result | 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
>0sums the matches per row, which gives the cell count again.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count rows where any column meets a condition
G2, count the students who scored at least G1 on any of the three tests.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).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.