Previous Topic Next Topic

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

Thanks in advance for your answer!
Related:

1 response

Answer 1 / 1
rayh
Nov 17, 2015 at 10:51 AM
Use this formula:
=COUNTIF(range,"a1")
0
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*")
Please help me out with this.

Thanks
Arzan
0
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.
0
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
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses
formulas for 1st and 2nd position in class
jack -
suff954 -

1 response