How to Count Rows That Meet Every Condition with AND Logic in Excel

Multiply TRUE/FALSE tests inside SUMPRODUCT to count rows that pass every condition — including ones COUNTIFS can’t handle.

You will learn to

• Count rows meeting two conditions by multiplying tests
• Mix OR (addition) into AND (multiplication)
• Compare two columns row by row

How it works

In Excel, TRUE × TRUE = 1 and anything × FALSE = 0. So multiplying conditions gives AND logic: =SUMPRODUCT((B2:B9="West")*(C2:C9>=10000)) counts rows where both are true. Adding conditions gives OR logic, because the sum is at least 1 if either is true.

This boolean approach does things COUNTIFS cannot: an OR group inside an AND, or a comparison between two columns such as deal size versus forecast, (C2:C9>E2:E9). It works in every version of Excel and makes the logic visible in one formula.

=SUMPRODUCT((B2:B9="West")*(C2:C9>=10000))