Count Frequency of a word in a column

[niravrph]

Hi all,

I have a list of text in a column (616 rows long) similar to the below. Can someone please tell me what formula/function I would use to count the number of times a respondent selected (e.g.) "ADA"? Some people said ADA while others said American Diabetes Association. I need to count both as being the same thing and get a total count.

Thank you! - Nirav

Make a Wish Foundation
Habitat for Humanity
ADA
Northern Illinois Diabetes Coalition
NPR
American Diabetes Foundation
American Diabetes Association
A women's/children shelter
JDRF
ADA
St. Judes or JDRF
JDRF
American Cancer
JDRF or Diabetes Youth Foundation (DYF), camp in Indiana.
Susan G Komen Breast Cancer Awareness
Samaritans Purse
ADA
ADA
american diabetes association
JDRF, Penn State Diabetes and Obesity Center.
American Diabetes Association, Humane Society, Veterans
RED CROSS
KaBoom.org
ADA
American Diabetes Association
American Cancer Association
ADA

 

[Aladin Akyurek]

Try...

=SUM(COUNTIF(A2:A28,{"ADA","American Diabetes Association"}))

 

[T. Valko]

Hi all,

I have a list of text in a column (616 rows long) similar to the below. Can someone please tell me what formula/function I would use to count the number of times a respondent selected (e.g.) "ADA"? Some people said ADA while others said American Diabetes Association. I need to count both as being the same thing and get a total count.

Thank you! - Nirav

Make a Wish Foundation
Habitat for Humanity
ADA
Northern Illinois Diabetes Coalition
NPR
American Diabetes Foundation
American Diabetes Association
A women's/children shelter
JDRF
ADA
St. Judes or JDRF
JDRF
American Cancer
JDRF or Diabetes Youth Foundation (DYF), camp in Indiana.
Susan G Komen Breast Cancer Awareness
Samaritans Purse
ADA
ADA
american diabetes association
JDRF, Penn State Diabetes and Obesity Center.
American Diabetes Association, Humane Society, Veterans
RED CROSS
KaBoom.org
ADA
American Diabetes Association
American Cancer Association
ADA

Assuming that both ADA and American Diabetes Association will not be in the same cell.

Use cells to hold the criteria:

• B2 = ADA
• C2 = American Diabetes Association

Then:

=SUMPRODUCT(--(ISNUMBER(SEARCH(B2:C2,A2:A28))))

Note that in this application the criteria must be in a horizontal range.

 

[Aladin Akyurek]

Try...

=SUM(COUNTIF(A2:A28,{"ADA","American Diabetes Association"}))

In order to also capture the partial occurrences...

=SUM(COUNTIF(A2:A28,"*"&{"ADA","American Diabetes Association"}&"*"))

 

[niravrph]

Fantastic!! Thank you very much!!
You guys are the best resource on the internet!!

- Nirav