# SUMPRODUCT count multiple OR criteria

=SUMPRODUCT(ISNUMBER(MATCH(rng1,{"A","B"},0))*ISNUMBER(MATCH(rng2,{"X","Y","Z"},0)))

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.

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:

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:

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.

## 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.