How do I count the number of times a text string appears in a column of cells?

[red_leader]

I have a column that uses a macro (thanks to Chandoo!) to allow for more than one name to be selected from a drop down and added to the cell. What I need is a formula that will scan the column and count how many times a name appears in that column, and then give me a total for each name on a separate worksheet so that I can then graph the numbers. This seems like it should be simple, but I cannot figure out how to do it. Sometimes I think I'm good at Excel, and then I'm brought face-to-face with my true hackerness... Any help you can provide would be greatly appreciated. Thanks!

 

[prasaddn]

Hi Red_leader,


Welcome to Chandoo.org!!


I am not clear on your requirements (layout of file). Do you have all names in one cell or each name in each cell of column?


If it is 2nd way, then you can plot a pivot table manually and set the layout you want. Then after you have your macro to add names in the column, you need to just refresh the PT. Use dynamic list size using defined name as source for your graph.


Regards,

Prasad DN

 

[Luke M]

I like prasad's idea best about using the PivotTable. If there's only a few names, or a pre-defined set of names that you care about, the function you are looking for is COUNTIF.


=COUNTIF(Sheet1!A:A,"Chandoo")

 

[red_leader]

I have multiple names in one cell;, i.e, cell A1 contains the names "Chandoo, Luke, Prasad", cell A2 contains the names "Ninja, Luke, Darth", and cell A3 contains the single name "Darth". I need a formula that will look at the contents of each cell in column A and tell me the number of times the name "Darth" appears. I've tried using countif(A1:A3,"Darth"), but it only picks up the cells where Darth is the ONLY name, so I get a count of 1 when I should get 2.


Thanks for the welcome, BTW - have been a reader for awhile now and get the daily emails, but this is my first post/cry for help.

 

[Luke M]

Ah, that's a horse of a different color! =P


Try this:

=SUMPRODUCT(1*(ISNUMBER(SEARCH("Darth",A1:A1000))))


PS. If you need this to be case-sensitive, replace SEARCH with FIND.

 

[Faseeh]

Hi red_leader,


Is it possible to add a helper column? if yes then you can use this, Column A contian strings, B is helper one:

[pre]

String	Helper	Count
D,B,C	0	3
A,B,D	1
A,B,E	1
A,B,F	1[/pre]
In helper column from B2 [code]=IFERROR(SEARCH("A",A1,1),0)

In C2 =COUNTIF(B1:B4,">0&#34[/code]


Hope it helps


Faseeh

 

[red_leader]

Luke -


Awesome!! The force is truly with you, sir - this works great! Thanks a bunch -

 

[Luke M]

red_leader,


Glad to be of help. Thanks for the feedback. =)