User Rating: 5 / 5     COUNTIF for Non-Contiguous Cells, COUNTIF with Wildcard Characters, COUNTIF for Date Range(s)

COUNTIF for Non-Contiguous Cells in "Range" or "Criteria", COUNTIF with Wildcard Characters, COUNTIF for Date Range(s), with AND / OR criteria

Column H formulas use Countif or Sumproduct functions to do a COUNTIF for Non-Contiguous Cells in "Range" or "Criteria", COUNTIF with Wildcard Characters, COUNTIF for Date Range(s). Out of 17 formulas in column H, 4 formulas are being illustrated here.

Cell H4 formula: Using COUNTIF with CHOOSE function: Count where Col B is EITHER cell E4 OR cell F5 OR cell G6 (ie. 3 non-contiguous cells as criteria).

Cell H14 formula: COUNTIF for the 3 non-contiguous ranges "B2:B5", "B9" & "B14:B16" with "Car" criteria.

Cell H22 formula: Uses wildcard Asterisk (*) which matches any sequence of characters: COUNTIF for column C values starting with "U" where col B cells are non-blank.

Cell H30 formula: Using COUNTIF for Dates within a Date Range - count where Column A dates are between 1st May 2017 AND 31st July 2017.

-----------------------------------------------------------------------------------------------

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

Non-Contiguous "Criteria" Cell References in COUNTIF - Cell H4 formula uses COUNTIF with CHOOSE function: Count where Col B is EITHER cell E4 OR cell F5 OR cell G6 (ie. 3 non-contiguous cells as criteria).

Cell H4 formula:

=SUMPRODUCT( COUNTIF( B2:B20, CHOOSE({1,2,3}, E4,F5, G6)))

Explanation - Cell H4 formula (Non-Array Formula):

1) Using the CHOOSE function - CHOOSE({1,2,3}, E4, F5,G6) - returns the 3 chosen values (1 to 3) of the 3 cells mentioned ie. {"Car", "Metro","Bike"}.

Non-Contiguous Cell References for "Range" in COUNTIF - Cell H14 formula uses COUNTIF with INDIRECT to COUNT the 3 non-contiguous ranges "B2:B5", "B9" & "B14:B16" for "Car" criteria.

Cell H14 formula:

=SUMPRODUCT( COUNTIF( INDIRECT({"B2:B5", "B9", "B14:B16"}), "Car"))

Explanation - Cell H14 formula (Non-Array Formula):

1) Using INDIRECT as the ref_text argument of COUNTIF function creates a range of cell references amenable to counting per given criteria.

Using COUNTIF with wildcard characters - Cell H22 formula does a COUNTIF using wildcard Asterisk (*): Count col C values starting with "U" where col B cells are non-blank.

Cell H22 formula:

=COUNTIFS( C2:C20, "U*", B2:B20, "<>")

Explanation - Cell H22 formula (Non-Array Formula):

1) Using the wildcard character Asterisk (*) matches any sequence of characters.

Using COUNTIF with Dates Range - Cell H30 formula uses COUNTIF for Dates within a Date Range - count where Column A dates are between 1st May 2017 AND 31st July 2017

Cell H30 formula:

=COUNTIFS( A2:A20, ">="& DATE(2017,5,1), A2:A20,"<="& DATE(2017,7,31))

Explanation - Cell H30 formula (Non-Array Formula):

1) Uses the DATE function to determine that dates are between 1st May 2017 AND 31st July 2017.

Other formulas:

Non-Contiguous "Criteria" Cell References in COUNTIF

Cell H6 formula: COUNTIFS formula, with Hardcoded arguments: Count where Col A = cell E2 AND Col B is EITHER "Car" OR "Metro" OR "Bike".

Cell H7 formula: Using COUNTIFS with CHOOSE function: Count where Col A = cell E2 AND Col B is EITHER cell E4 OR cell F5 OR cell G6.

Cell H8 formula: Using multiple COUNTIFS: Count where Col A = cell E2 AND Col B is EITHER cell E4 OR cell F5 OR cell G6.

Cell H9 formula: Criteria Cell References which can be standardized, using COUNTIF with MOD function: Count where Col B is EITHER cell E8 OR cell E10. Array Formula (Ctrl+ Shift+ Enter).

Non-Contiguous Cell References for "Range" in COUNTIF, using the volatile INDIRECT / OFFSET functions with COUNTIF

Cell H13 formula: Uses INDIRECT with COUNTIF and a named range "RngNonContg" (E12:E14) - COUNT the 3 non-contiguous ranges "B2:B5", "B9" & "B14:B16" for "Car" criteria.

Cell H15 formula: Uses cell references (E16:E17) which contain the actual 2 non-contiguous ranges "B2:B8" & "B9:B11" to COUNT for "Car" criteria - uses INDIRECT function with COUNTIF.

Cell H16 formula: Uses OFFSET with COUNTIF to COUNT the 3 non-contiguous ranges "B2:B5", "B9" & "B14:B16" for "Car" criteria.

Using COUNTIF with wildcard characters

Cell H23 formula: Uses wildcard Asterisk (*) which matches any sequence of characters: Count col C values ending with "land" where col B cells are non-blank.

Cell H24 formula: Uses wildcard Question mark (?) which matches any single character: Count col C values with any single character bewteen "I" & "eland" & non-blanks in col B.

Cell H25 formula: Uses wildcard Question mark (?) which matches any single character: Count col C values with any 3 characters before "land" & non-blanks in col B.

Cell H26 formula: Uses Tilde wild card ("~") which allows searching for words that contain a wild card (* or ?). Placing tilde before the asterisk tells Excel that asterisk should not be used as a wildcard. Count col C values which start with "*F" and where col B = "Metro".

Using COUNTIF with Dates Range

Cell H31 formula: Uses COUNTIF for Dates within a Month / Year - Count where Column A dates are in the Month & Year of cell H28 date and column B cells are not blank.

Cell H33 formula: Uses COUNTIF for Dates in Calendar Year - COUNT where Column A dates are in the Year (01 Jan-31 Dec) of cell H28 date & Column B cells are not blank.

Only four of the seventeen formulas are illustrated above. For full details and explanation refer to Ebook of Excel Formulas.