# Formula to count the duplication of a value in a range

Closed
ARZANM Posts 9 Registration date Tuesday November 17, 2015 Status Member Last seen December 8, 2015 - Nov 17, 2015 at 07:32 AM
ARZANM Posts 9 Registration date Tuesday November 17, 2015 Status Member Last seen December 8, 2015 - Nov 18, 2015 at 11:22 PM
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

Related:

## 1 response

Use this formula:
=COUNTIF(range,"a1")
ARZANM Posts 9 Registration date Tuesday November 17, 2015 Status Member Last seen December 8, 2015
Nov 17, 2015 at 11:37 PM
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*")

Thanks
Arzan
rayh > ARZANM Posts 9 Registration date Tuesday November 17, 2015 Status Member Last seen December 8, 2015
Nov 18, 2015 at 11:29 AM
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.
ARZANM Posts 9 Registration date Tuesday November 17, 2015 Status Member Last seen December 8, 2015
Nov 18, 2015 at 11:22 PM
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