Count Rows Meeting Any of Several Conditions

Excel Formulas › Count

All versionsSUMPRODUCT

Count rows that match condition A or B without double-counting the ones that match both. SUMPRODUCT with SIGN caps each row at one.

Quick formula: count rows where status is “Open” OR priority is “High”:
=SUMPRODUCT(SIGN((status="Open") + (priority="High")))
Add the condition arrays, then SIGN turns any positive (1 or 2 matches) into 1 — so a row matching both counts once.

Functions used (tap for the full reference guide):

SUMPRODUCTCOUNTIF

The example

Rows that are Open or High (or both) counted once each.

AB
1StatusPriority
2OpenHigh
3DoneHigh
4OpenLow
5Open OR High3

The formula

Add conditions, cap with SIGN:

=SUMPRODUCT(SIGN((status="Open") + (priority="High"))) // no double-count

How it works

SIGN prevents double-counting:

  1. Each condition is a 1/0 array; adding them gives 0, 1, or 2 per row.
  2. A row matching both conditions would be 2 — SIGN turns any positive into 1.
  3. SUMPRODUCT sums the capped values — the count of rows matching at least one condition.
  4. Simpler when conditions are on the same column: =COUNTIF(rng,"A")+COUNTIF(rng,"B") (no overlap possible there).

Inclusion-exclusion alternative: =COUNTIF(A,"Open") + COUNTIF(B,"High") − COUNTIFS(A,"Open",B,"High") — add the two counts, subtract the overlap. The SIGN version is tidier for many conditions.

Try it: interactive demo

Live demo

Lines “status,priority”; count Open OR High.

Count:

Variations

Inclusion-exclusion

Add minus overlap:

=COUNTIF(a,"Open")+COUNTIF(b,"High")-COUNTIFS(a,"Open",b,"High")

Same column OR

No overlap:

=COUNTIF(rng,"A")+COUNTIF(rng,"B")

Three conditions

Add another array inside SIGN.

Pitfalls & errors

SIGN stops double-counting. Without it, a row matching both conditions adds 2. Always cap multi-column ORs.

Equal-length arrays. Every condition array must span the same rows.

Same-column OR is simpler. Two values in one column can’t both match, so plain addition is safe there.

Practice workbook

📊
Download the free Count Rows Meeting Any of Several Conditions practice workbook
An OR-count sheet with the inclusion-exclusion, same-column, and three-condition variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count rows meeting any of several conditions in Excel?
Use =SUMPRODUCT(SIGN((cond1) + (cond2))). Adding the condition arrays and capping with SIGN counts rows matching at least one, without double-counting.
Why use SIGN?
A row matching both conditions would add 2. SIGN turns any positive into 1, so each row counts once.
What's the inclusion-exclusion version?
Add the two counts and subtract the overlap: =COUNTIF(A,x)+COUNTIF(B,y)-COUNTIFS(A,x,B,y).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Count with criteria · SUMPRODUCT formula · FILTER with OR

Function references: SUMPRODUCT · COUNTIF