SUMPRODUCT count multiple OR criteria in Excel

This tutorial shows how to work SUMPRODUCT count multiple OR criteria in Excel using the example below;

Formula

=SUMPRODUCT(ISNUMBER(MATCH(range1,{"A","B"},0))*ISNUMBER(MATCH(range2,{"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)))