Formula to count the duplication of a value in a range [Closed]

Report
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015
-
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015
-
Hello,

I am looking for a formula that can count the number of duplication of a particular value in a range of cells.
Example: I need to know the count of a1 in the below range.

A1: a1 a2 b1
A2: a1 a3 c2
A3: a1 c3 a1
A4: c2 a1 a1
A5: a1 b3
A6: a1 a1

Thanks in advance for your answer!

1 reply

Use this formula:
=COUNTIF(range,"a1")
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015

Thank you Rayh but I have already tried this formula and the answer I am getting is 6 whereas the correct answer is 9 (A3 has two a1's, A4 has two a1's & A6 has two a1's). I also tried the below formula but still I am not getting the proper answer.
=COUNTIF(range,"*a1*")
Please help me out with this.

Thanks
Arzan
>
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015

I get a result of 9 so I'm not sure what to tell you other than it works for me.

Even if I replace "a1" with "x a1 x" in the cells and use:
=COUNTIF(range,"*a1*")
I still get an answer of 9.

Perhaps if you post your spreadsheet to a site like speedyshare. This way we can see what's really going on.
Sorry I'm not more help.
Posts
9
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
December 8, 2015

Hi Rayh,

Thanks for the revert but am still getting 6 as the answer. Forgot to mention that I am using Excel 2007. Anyway I will try and post it on speedyshare as you suggested.

Thanks
Arzan

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!