Print

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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