Frequency formula

[srizki]

I have names in column A, and frequency in column B, some fames are repeated 10 times while most of them are only once. I have count if formula in column B, I need a formula that adds up the frequency only once. So if let’s say name UK appears 7 times, US 11 times and Pakistan 4 times, Sweden 4 times, it should add 7, 11, 4, and 4 not 7+7+7+7 etc.
Thanks,

 

[tyija1995]

Hey, assuming some sample data being in cells A1:A17 try this formula in B1 and drag down: (adjust accordingly)

=IF(MATCH(A1,$A$1:$A$17,FALSE)=ROW(),COUNTIF($A$1:$A$17,A1),"")

It will show the frequency only once (first appearance of the country name)

 

[srizki]

I think your formula is correct, my data is from A to A62 and I am using your formula as below,
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64"]=IF(MATCH(A2,$A$2:$A$62,FALSE)=ROW(),COUNTIF($A$2:$A$62,A2),"")
with array and without array but it gives me blank cells.

Thanks[/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

I've just tested it and my formula doesn't like if the data is not starting from the top row, so try this amended formula instead:

In cell B2:
=IF(MATCH(A2,$A$2:$A$62,FALSE)=ROWS($A$2:A2),COUNTIF($A$2:$A$62,A2),"")

The issue with the initial formula was the =ROW() part as its starting on the 2nd row its looking for something that will never happen, the new formula provided should fix this :D

I should have spotted it first time around as most people do indeed have header rows

 

[srizki]

Excellent, Thanks
it is working as I wanted.

 

[DanteAmor]

Just another way

=IF(COUNTIF(A2:$A$62,A2) < COUNTIF($A$2:$A$62,A2),"",COUNTIF($A$2:$A$62,A2))<countif($a$2:$a$62,a2),"",countif($a$2:$a$62,a2))< html=""></countif($a$2:$a$62,a2),"",countif($a$2:$a$62,a2))<>

 

[srizki]

Thank you very much DanteAmor.

This is easier than the other one, but both are working.

 

[DanteAmor]

Thank you very much DanteAmor.

This is easier than the other one, but both are working.

I'm glad to help you. Thanks for the feedback.