Countif with Array Criteria, using AND / OR criteria

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  

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top