User Rating: 5 / 5     COUNTIF with Array Criteria, using OR / AND criteria

COUNTIF with Array Criteria & OR / AND: Combine criteria from multi-columns using OR and AND

Column G formulas use COUNTIF or SUMPRODUCT, OR MMULT or FREQUENCY functions to count with multiple criteria, using AND / OR conditions ie. count if either of the multiple conditions is satisfied, as described below.

Cell G7 formula: Countif: hardcoded values - if column A = "Jack" OR "Jill". Consider if EITHER of the 2 values are present in column A.

-----------------------------------------------------------------------------------------------

Cell G7 formula uses Countif with  hardcoded values for the condition - if column A = "Jack" OR "Jill" - and returns the Count if EITHER of the 2 values are present in column A.

Cell G7 formula:

=SUM(COUNTIF( A2:A15, {"Jack", "Jill"}))

Explanation - Cell G7 formula (Non-Array Formula):

1) COUNTIF(A2:A15, {"Jack", "Jill"}): using an array criteria for a range, COUNTIF returns an array of values - {2,2} - the numbers indicate the respective occurrence(s) of each of the 2 values ("Jack" & "Jill") in column A, and using SUM returns the total number of times each of them occur in column A.

-----------------------------------------------------------------------------------------------

Related Formulas - illustrated in the Ebook of Excel Formulas.

Cell G4 formula: Countifs: hardcoded values; if column A = "Jack" AND column B = "Jill"; Consider only if BOTH values are present in same row of respective columns A & B.

Cell G5 formula: Sumproduct: cell references; if column A = F4 AND column B = F5; Consider only if BOTH values are present in same row of respective columns A & B.

Cell G11 formula: Countif: cell references - if column A = F4 OR F5.  Consider if EITHER of the 2 values are present in column A. Similar logic as cell G7 formula.

Cell G12 formula: Sumproduct: cell references - if column A = F4 OR F5.  Consider if EITHER of the 2 values are present in column A. Similar logic as cell G7 formula.

Cell G13 formula: Countif: hardcoded values - if column A (A2:A15) = Blank OR "Jack" OR "Jill". Similar logic as cell G7 formula, except that another option of blank cells is added and counted.

Cell G15 formula: Countifs: hardcoded values - if column A = "Jack" OR "Jill" AND column B = "Mary". Counts occurrences of (i) column A = "Jack" AND column B = "Mary" in same row;  OR (ii) column A = "Jill" and column B = "Mary" in same row; (iii) both i) and ii) counts are added.

Cell G17 formula: Countifs: hardcoded values - if column A = "Jack" OR "Jill" AND column B = "Mary". This is not ideal for large number of multiple values. Similar logic as cell G15 formula.

Cell G18 formula: Countifs: cell references - if column A = F4 OR F5 AND column B = F6. Similar logic as cell G15 formula.

Cell G20 formula: Countifs: hardcoded values - if column A = "Jack" OR "Jill" AND column B = "Mary" AND column C = "Stacy".

Cell G22 formula: Countifs: cell references - if column A = F4 OR F5 AND column B = F6 AND column C = F7".

Cell G25, G30, G35, G37 formulas: Count Multiple Columns for Occurrence of Same Value: Count if column D = "John" AND atleast one of the columns A, B & C = "John". Cell G25 formula uses SUMPRODUCT - Normal (non-array) formula. Cell G30 formula uses MMULT: Array formula (Ctrl +Shift +Enter). Cell G35 formula uses MMULT - NonArray formula. Cell G37 formula uses FREQUENCY: Array formula (Ctrl +Shift +Enter).

Note: COUNTIFS / SUMIFS functions are generally more efficient than using SUMPRODUCT.