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.
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.
