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!

Formula to count the duplication of a value in a range
Date formula in excel dd/mm/yyyy - Guide
Number to words in excel formula - Guide
Logitech formula vibration feedback wheel driver - Download - Drivers
How to duplicate a google doc - Guide
Count the number of times a value appears in a column ✓ - Office Software Forum

1 response

Answer 1 / 1

rayh
Nov 17, 2015 at 10:51 AM

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*")
Please help me out with this.

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

Similar discussions

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

Counting cells containing only numbers

How to calculate position (1st,2nd,3rd) in excel....?

Calculation of positions in excel

formulas for 1st and 2nd position in class

Similar discussions

Subscribe To Our Newsletter!