Jul 05 2021 06:43 AM
Hi All:
Trying to count how many times the word "apple" appears in a column where the word "apple" can be separated by "," or ";"? Example see below.
The value I expect is that the count of the word "apple" should be = 5. Thank you!
apple; apple |
apple |
pear |
apple, apple |
Jul 05 2021 06:52 AM
SolutionLet's say that the data are in A1:A4.
=SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"apple","")))/LEN("apple")
Jul 05 2021 07:04 AM
Jul 05 2021 07:07 AM
The COUNT function returns the number of cells in a range that have a numeric value. It cannot be used to count text values.
You can use =COUNTIF(A1:A4,"*apple*") to count the number of cells that contain the word "apple". But it will count each such cell only once.
Jul 05 2021 06:52 AM
SolutionLet's say that the data are in A1:A4.
=SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"apple","")))/LEN("apple")