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 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 G7 formula: Countif: hardcoded values - if column A = "Jack" OR "Jill". Consider if EITHER of the 2 values are present in column A.

 

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. 

 

 

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

 

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

       

"Open Image in New Tab" for a full and clear view. 

 

 

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.  

 

 

Other column G formulas are as mentioned above. They use COUNTIF, SUMPRODUCT, MMULT or FREQUENCY to return the COUNT as per given criteria.

 

  

    

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.