Count IF question

[uniqueimportsm6]

Hello,
I believe I would use a Count IF or/and Sum IF function for this question, but I am not sure and am new to excel. Thanks for your help.

I have one long column filled with the names of people. I need to count the number of people that show up once and the people that show up more than once. So I am looking for any name that shows up more than once or just once and not the specific name of the person. I can use two different formulas for each result I am looking for.


For instance(column):
Patel
Patel
Patel
Smith
Myers
Kaplan
Jones
Jones

I need to come up with an answer that shows that 2 people have their names show up more than once and that 3 peoples names show up once.

[daddylonglegs]

Let's assume your names are in A1:A100

Use this formula to give you a count of names that appear only once

=SUMPRODUCT(--(COUNTIF(A1:A100,A1:A100)=1))

and for a count of names that appear more than once

=SUMPRODUCT(--(COUNTIF(A1:A100,A1:A100)>1)/COUNTIF(A1:A100,A1:A100&""))

[uniqueimportsm6]

Hello,

When use the data above:
Patel
Patel
Patel
Smith
Myers
Kaplan
Jones
Jones

1. The first formula for the number of names that appears once =SUMPRODUCT(--(COUNTIF(A1:A100,A1:A100)=1))
gives me an answer of 4. I am looking for an answer of 3 ie three peoples names show up once.

2. The second formula works. Thanks!

[rasonline]

Hello,


1. The first formula for the number of names that appears once =SUMPRODUCT(--(COUNTIF(A1:A100,A1:A100)=1))
gives me an answer of 4. I am looking for an answer of 3 ie three peoples names show up once.

if you have a heading in the column, then the formula might be including that item in the answer?

[uniqueimportsm6]

if you have a heading in the column, then the formula might be including that item in the answer?

I took the actual information above and tested in excel without headings and this is the answer I keep getting.

[darkyam]

I get 3. Try using the Len() formula on these names. Maybe you have an extra space after one of them or something.

[uniqueimportsm6]

I get 3. Try using the Len() formula on these names. Maybe you have an extra space after one of them or something.

Your right I just did it again in a new sheet and I am getting the right answer. I think it could have been an extra space. Thanks

[uniqueimportsm6]

SOLVED.. thanks a lot for your guys help.