User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

COUNTIF for Non-Contiguous Cells, COUNTIF with Wildcard Characters, COUNTIF for Date Range(s)

 

 

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

   

 

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

 

 

Countifs - Non-Contiguous Cells, Wildcards, Date Range 

         

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