User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

COUNTIF with Array Criteria, using OR / AND criteria

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

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.

 

Countif Array Criteria, OR condition  

 

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.