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

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

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

"Open Image in New Tab" for a full and clear view.

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.

Other column H formulas are as mentioned above. They use COUNTIF or SUMPRODUCT to return the COUNT as per given criteria.

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.