Excel Formulas - Count number of rows containing multiple OR condition

Count number of rows containing multiple OR condition

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.

What to do?

To count number of rows from a table where multiple OR condition can be matched, the SUMPRODUCT have used.

Formula

=COUNTIFS(C6:C11,"IX",D6:D11,">70")

How this formula works

In the above formula the C5:C10="IX" checks whether the range C5:C10 contains any "IX" and returns an array of TRUE FALSE value like

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

and the second condition D5:D10="M" checks whether the range contains any "M" and returns an array of TRUE FALSE value like

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

These two arrays are then joined with addition (+), which automatically convert the TRUE FALSE values to 1 and 0 to create an array like this:

{2;1;1;0;2;0;}

Therefore the ">0" together with the double negative (--) have used to force all values to either 1 or zero, and the array like

{1;1;1;0;1;0;}

Now the SUMPRODUCT function make a sum of the array and returns the result.

Previous: Excel Formulas - Count rows matching two criterias in two columns within a row
Next: Excel Formulas - Count number of occurences within mulitple worksheets in a workbook