Exceljet

Quick, clean, and to the point

SUMPRODUCT count multiple OR criteria

Excel formula: SUMPRODUCT count multiple OR criteria
Generic formula 
=SUMPRODUCT(ISNUMBER(MATCH(rng1,{"A","B"},0))*ISNUMBER(MATCH(rng2,{"X","Y","Z"},0)))
Explanation 

To count matching rows with multiple OR criteria, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in F10 is:

=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,{"A","B"},0))*
ISNUMBER(MATCH(C5:C11,{"X","Y","Z"},0)))

This formula returns a count of rows where column one is A or B and column two is X, Y, or Z.

How this formula works

Working from the inside out, each criteria is applied with a separate ISNUMBER + MATCH construction. To generate a count of rows in column one where the value is A or B we use:

ISNUMBER(MATCH(B5:B11,{"A","B"},0)

MATCH generates a result array that looks like this:

{1;2;#N/A;1;2;1;2}

and ISNUMBER converts this array to this array: 

{TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}

To generate a count of rows in column two where the value is X, Y, or Z we use:

ISNUMBER(MATCH(C5:C11,{"X","Y","Z"},0))

Then MATCH returns:

{1;2;3;3;#N/A;1;2}

and ISNUMBER converts to:

{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}

These two arrays are multiplied together inside SUMPRODUCT, which automatically converts TRUE FALSE values to 1 and 0 as part of the math operation.

So, to visualize, the final result is derived like this:

=SUMPRODUCT({1;1;0;1;1;1;1}*{1;1;1;1;0;1;1})
=SUMPRODUCT({1;1;0;1;0;1;1})
=5

With cell references

The example above uses hardcoded array constants, but you can also use cell references:

=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))

More criteria

This approach can be "scaled up" to handle more criteria. You can see an example in this formula challenge.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables