How to count values with OR operator

You can count values in Excel by multiple criteria using the COUNTIFS function. However COUNTIFS will only link those criterion by AND operation. If you want to COUNTIFS OR, you need an alternative approach. This article shows how to count values with OR operator using the SUMPRODUCT function.

Syntax

=SUMPRODUCT((criteria range 1 = criteria 1)+(criteria range 2 = criteria 2)+…)

Steps

Start with =SUMPRODUCT( function
Enter criteria range – criteria condition in parenthesis (D3:D11="Grass")
Type plus sign (+) to apply OR condition
Enter other criteria range – criteria condition in parenthesis (D3:D11="Water")
Type ) to close and finish the formula

How

With SUMPRODUCT's ability to handle arrays without array formulas provides an advantage over the limitations of COUNTIF and COUNTIFS functions. The SUMPRODUCT can evaluate and return arrays from range – value conditions. We use this functionality resolve criteria range – criteria pairs. This allows us to handle COUNTIFS OR combination without using COUNTIFS formula.

(D3:D11="Grass") returns {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Also, SUMPRODUCT sums values in its argument array if there is only one argument. However, conditional equations returns Boolean values. So, they need to be converted into numbers which are meaningful for the SUMPRODUCT function. Fortunately, the add operator (+) does this.

Another and more important effect of the add operator (+) is to mimic the logical OR operator. In a logic, TRUE OR FALSE statement returns TRUE. This behavior can be used by the add operator (+) in terms of 1 and 0s. If 1 and 0 represent TRUE and FALSE respectively, 1 + 0 = 1 equation acts like TRUE OR FALSE = TRUE statement as well. As a result;