The challenge is the COUNTIFS function applies AND logic by default.

By default, the COUNTIFS function applies AND logic.

Note: this technique will only handle two range/criteria pairs.

Excel formula: Count cells equal to this or that

If you have more than two criteria,consider a SUMPRODUCT formula as described here.

This formula “just works” in the current version of Excel, which supportsdynamic array formulas.

However, inLegacy Excel, the formula must be entered with Control + Shift + Enter.

Excel formula: Count cells not equal to x or y

Excel formula: Count cells that contain either x or y

Excel formula: SUMPRODUCT count multiple OR criteria

Excel formula: Count matching values in matching columns

Excel COUNTIFS function

Article image