Countif only once

[Anto111]

I Have a worksheet which I am adding rows to as i get information in.

One of the columns is country, what I want to do is countif so i know the
number of times that a country appears. Basically I am using a simple countif
formula in the adjacent cell to that specific row but extending the range so
it automatically counts new data when it is entered. For example my data
currently ranges from C1:C11 but my range is C1:C111 so as to automatically
pick up new data as it is entered. There is also not a set range of countries
so a new country might appear in C34 lets say.

What I need to avoid is the countif function repeating, so for example if
"Greece" appears in C4 and C8 then My countif formula in D4 will return 2 for
Greece... perfect, however the formula will once again return 2 in C8, I want
to avoid this so as the countif function is used once per country.

I hope this is clear, I'm in abit of a rush today.

Many thanks in adance for your help,

Kind regards,

Ant

 

[Dylan]

Are you using =COUNTIF($C$1:$C$111, "Greece")

If the formula is in D4, why would it return 2 in C8 if C8 contains "Greece".

 

[MartinW]

Hi Ant,

Put this in D1 and drag down,

=IF(COUNTIF(C$1:C1,C1)>1,"",COUNTIF(C$1:C$111,C1))

That should put each countries count at the first occurrence of
each country.

HTH
Martin

 

[Dave Peterson]

Count the occurrences, but then hide the results if it's more than 1.

=IF(COUNTIF($C$1:C1,C1)=1,COUNTIF(C:C,C1),"")
and drag down

 

[MartinW]

Also,

If there is likely to be any blanks then you may want to use this
=IF(C1="","",IF(COUNTIF(C$1:C1,C1)>1,"",COUNTIF($C$1:$C$111,C1)))

otherwise you will get a zero next to the blank cells.

HTH
Martin