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

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

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))

--
Regards,
Dave


"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

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

Thanks to both, worked great

"David Billigmeier" wrote:

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))

--
Regards,
Dave


"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