User Rating: 5 / 5     COUNTIF with Array Criteria, Multiple Columns, AND / OR conditions

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

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