Counting
Note: If the text to find will ONLY occur ONCE in a cell, there are shorter
formulas to do that. The formula I offered counts ALL instances in the
referenced cells, even if the text appears several times:
Example:
A1: Alpha
D1: Alpha and Beta
D2: Alpha, Beta, Alpha
Count of Alpha is 3.
=SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)
Note: SUBSTITUTE is case sensitive, hence the UPPER function.
***********
Regards,
Ron
"Ron Coderre" wrote:
Try this:
For the searched text in cell A1 and a list of cells that contain text in
D1:D10
=SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)
Does that help?
***********
Regards,
Ron
"bjenkins" wrote:
Is there a non-macro way to count if words appear in a cell.
In other words, I have a list in excel such as the following
Alpha
Beta
Gamma
Alpha, Beta
Alpha, Delta
Delta, Gama
and I want to count the number of times that Alpha, Beta, Gamma, and Delta
appear so that it would produce
Alpha - 3
Beta - 2
Gama - 2
Delta - 2
Thanks in advance for the help
|