COUNTIF with Array Criteria, Multiple Columns, AND / OR conditions
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 (or Sumproduct) for Multiple Columns, Array Criteria, using AND / OR conditions
Column H formulas use COUNTIF or SUMPRODUCT functions to Count with respect to Multiple Columns and multiple criteria, using AND / OR conditions ie. count if either of the multiple conditions is satisfied, as described below.
Cell H3 formula: COUNTIFS formula, hardcoded arguments: returns COUNT where column A = “DATE(2017,3,15)” AND column B is EITHER “Car” OR “Metro” AND column C is EITHER “France” OR “UK”.
Note: COUNTIFS / SUMIFS functions are generally more efficient than using SUMPRODUCT.
—————————————————————————–
Cell H3 formula uses COUNTIFS formula, with hardcoded arguments: returns COUNT where column A = “DATE(2017,3,15)” AND column B is EITHER “Car” OR “Metro” AND column C is EITHER “France” OR “UK”.
Countifs Multi Columns, Multi Criteria, OR condition
Cell H3 formula:
=SUM(COUNTIFS( A2:A20, DATE(2017,3,15), B2:B20, {“Car”, “Metro”}, C2:C20, {“France”; “UK”}))
Explanation – Cell H3 formula (Non-Array Formula):
1) Formula “COUNTIFS(A2:A20, DATE(2017,3,15), B2:B100, {“Car”, “Metro”}, C2:C20, {“France”; “UK”})” returns COUNT for “Car France, Metro France; Car UK, Metro UK” for the specified date, which equates to {1,1; 2,1}, and then SUM of these amounts is the final Formula result.
2) One of the array constants should be a single-column array, while the other should be a single-row array. In Excel, commas represent column-separators and semi-colons represent row-separators.
3) Having comma in one of the array constants while semi-colon in the other array constants has the effect to coerce Excel into a “two-dimensional” set of returns, so as to count all possible combinations for these two sets of criteria.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell H12 formula: COUNTIFS formula, Cell References: COUNT where Col A = E3 AND Col B is EITHER F3 or F4 AND Col C is EITHER G3 or G4.
Cell H15 formula: SUMPRODUCT formula, hardcoded arguments: returns COUNT where column A = “DATE(2017,3,15)” AND column B is EITHER “Car” or “Metro” AND column C is EITHER “France” or “UK”.
Cell H17 formula: SUMPRODUCT formula, Cell References: COUNT where Col A = cell E3 AND Col B is EITHER F3 or F4 AND Col C is EITHER G3 or G4.
Cell H20 formula: COUNTIFS formula, Cell References: COUNT where column A = cell E3 AND column B is EITHER cell F3 OR cell F4.
Cell H21 formula: COUNTIFS formula, hardcoded arguments: COUNT where col A = “DATE(2017,3,15)” AND col B is EITHER “Car” OR “Metro”.
Cell H22 formula: COUNTIFS formula, Cell References: COUNT where Col B is EITHER cell F22 or cell G22 AND Col C is EITHER cell G3 OR cell G4.
Cell H23 formula: COUNTIFS formula, Cell References: COUNT where Col B is EITHER cell F22 or F23 AND Col C is EITHER G3 OR G4. Array Formula.
Cell H24 formula: COUNTIFS formula, hardcoded arguments: COUNT where Col B is EITHER “Car” OR “Metro AND Col C is EITHER “France” OR “UK”.
Cell H26 formula: COUNTIFS formula, Cell References: COUNT where EITHER (Col B is cell F26 AND Col C is cell F27) OR (Col B is cell G26 AND Col C is cell G27).
Cell H27 formula: COUNTIFS formula, hardcoded arguments: COUNT where EITHER (Col B is “Car” AND Col C is “France”) OR (Col B is “Metro” AND Col C is “UK”).