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
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.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
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.