Count Occurrences of a Value in all cells of a Column, either individually or part of a string
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.
Count Occurrences of a Value in all cells of a Column, either as a whole or in part – value may be appear multiple times in a cell, separated by comma (or space)
Column D formulas Count Occurrences of a Value in all cells of a Column, either individually or part of a string, match may be case-sensitive or case-insensitive.
Cell D5 formula: Count all Occurrences of C5 value in column A, either individually or as part of string / number (Whole or Part Match of Value).
———————————————————————————————–
Cell D5 formula Counts all Occurrences of C5 value in column A, either individually or as part of string / number (Whole or Part Match of Value). Ex. a cell containing “55543” will count “5” as 3 times; cell containing “34, 3434, 43, 6734” will count “34” as appearing 4 times.
Occurrences of Value in Whole or in Part
Cell D5 formula:
=SUMPRODUCT( LEN( A1:A20)- LEN( SUBSTITUTE( A1:A20, C5, “”)))/ LEN(C5)
Explanation – Cell D5 formula (Non-Array Formula):
1) The formula substitutes cell C5 value with nothing (using SUBSTITUTE function), and then calculates the total number of characters, using LEN and SUMPRODUCT.
2) The aim is to calculate the difference in the number of characters between the original text and the text after removing all occurences of cell C5 value. Then dividing this difference with the number of characters in C5 – LEN(C5).
3) Note: Each digit of cell C5 value should appear continuously in the same order so that “3456” will not be considered as containing “356”.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell D10 / D13 formulas: Count all Occurrences of “and” in column A, either individually or as part of string / number (Whole or Part Match of text “and”). Ex. A cell containing “anderson” is counted as 1, “jack and and” is counted as 2. Cell D10 formula is case-sensitive (counts only “and” and will ignore “And”, “AND” etc). Cell D13 formula is case-insensitive (counts all occurrences of “and”, “And”, “AND” etc).
Cell D19 / D22 formulas: Count Occurrences of “and” in column A separated by space (appearing individually in a cell or as one of the values separated by a space), match entire text “and” (excludes consecutive appearances). Note: If “and” appears consecutively in a cell (ex. “jack and and”) it is counted as one if single space between the 2 “and” but will be counted as 2 if double space between the two “and”; if there are 3 “and” ex. “jack and and and” then 2 are counted ie. the 1st & 3rd; if “and” does not appear consecutively ex. “and joe and” it is counted as 2; cell containing “anderson” will not be counted. Cell D19 formula is case-sensitive while cell D22 formula is case-insensitive.
Cell D26 formula: Count Occurrences of “and” in column A separated by space (appearing individually in a cell or as one of the values separated by a space), match entire text “and” (includes consecutive appearances). Note: If “and” appears consecutively in a cell (ex. “jack and and”) it is counted as 2 irrespective of the number of blank spaces between the 2 “and”; if there are 3 “and” ex. “jack and and and” then 3 are counted; if “and” does not appear consecutively ex. “and joe and” it is counted as 2; cell containing “anderson” will not be counted. Formula is case-insensitive.
Cell D33 formula: Count Occurrences of C33 value in column A separated by comma, appearing individually in a cell or as one of the values separated by a comma, match entire C19 value (includes consecutive appearances). Note: Inbetween spaces between C33 value & comma are ignored. Ex. a cell containing “55,356, 66, 356 , 356, 356356” will count “356” as appearing 3 times; cell containing “356 356” will count “356” as zero.
Cell D44 / D48 formulas: Count Number of Cells with atleast One Occurrence of C44 value in column A separated by a comma – match entire C44 value. (i) Multiple occurrences of C44 within a cell are counted only ONCE (ie. count the no of cells with atleast 1 occurrence of C44 value); (ii) Inbetween spaces between C44 value & comma are ignored. Ex. a cell containing “99,45, 99, 34” will count “99” as appearing once; cell with “2,99 ,995” will count “99” as one; cell with “99 99” or “1,2,3,3995” will count “99” as zero. Cell D44 formula uses COUNT with FIND and SUBSTITUTE functions while cell D48 formula uses SUMPRODUCT and ISNUMBER with FIND and SUBSTITUTE functions.
Cell D54 / D58 formulas: Count Number of Cells with atleast One Occurrence of “and”, separated by space, in column A – match entire text “and”. Note: Multiple occurrences of “and” within a cell are counted only ONCE (ie. it counts the no. of cells with atleast 1 occurrence of “and”). Ex, a cell containing “and joe and” or “jack and and” will count “and” as appearing once; cell containing “anderson” will count “and” as zero. Cell D54 formula is case-sensitive while cell D58 formula is case-insensitive.
Cell D64 formula: Count Number of Cells with atleast One Occurrence of “and” in column A, either as a whole or as part of string, where a cell may have multiple occurrences. Note: Multiple occurrences of “and”, in whole or in part, within a cell are counted only ONCE (ie. it counts the no. of cells with atleast 1 occurrence of “and”). Ex, a cell containing “and joe and” or “jack and and” or “anderson” or “And” will count “and” as appearing once. Formula is case-insensitive.