Counting

[bjenkins]

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

 

[Ron Coderre]

Try this:
For the searched text in cell A1 and a list of cells that contain text in
D110

=SUMPRODUCT(LEN(D110)-LEN(SUBSTITUTE(UPPER(D110),UPPER(A1),"")))/LEN(A1)

Does that help?

***********
Regards,
Ron

 

[David Billigmeier]

Array entered (CTRL+SHIFT+ENTER), these will work:

=SUM(IF(ISNUMBER(SEARCH("Alpha",A1:A100)),1,0))
=SUM(IF(ISNUMBER(SEARCH("Beta",A1:A100)),1,0))
=SUM(IF(ISNUMBER(SEARCH("Gamma",A1:A100)),1,0))
=SUM(IF(ISNUMBER(SEARCH("Delta",A1:A100)),1,0))

 

[Ron Coderre]

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(D110)-LEN(SUBSTITUTE(UPPER(D110),UPPER(A1),"")))/LEN(A1)

Note: SUBSTITUTE is case sensitive, hence the UPPER function.


***********
Regards,
Ron

 

[bjenkins]

Thanks to both, worked great

Array entered (CTRL+SHIFT+ENTER), these will work:

=SUM(IF(ISNUMBER(SEARCH("Alpha",A1:A100)),1,0))
=SUM(IF(ISNUMBER(SEARCH("Beta",A1:A100)),1,0))
=SUM(IF(ISNUMBER(SEARCH("Gamma",A1:A100)),1,0))
=SUM(IF(ISNUMBER(SEARCH("Delta",A1:A100)),1,0))