Note: the configuration for MATCH may appear “reversed” from what you might expect.

This is necessary to preserve the structure of the source data in the results returned by MATCH, i.e.

we get back seven results, one for each row in B5:B11.

Excel formula: Count rows with multiple OR criteria

An #N/A error indicates a row where neither value was found.

With just a single array to process, SUMPRODUCT sums the array and returns a final result of 5.

You cansee an example in this formula challenge.

Excel formula: COUNTIFS with multiple criteria and OR logic

Note: The SUMPRODUCT function is used below for compatibility withLegacy Excel.

In the current version of Excel, you’re free to use theSUM functioninstead.

For more on this topic, see:Why SUMPRODUCT?

Excel formula: Count matching values in matching columns

Excel SUMPRODUCT function