Need a formula that tells me how many times a value is repeated in a range of cells

[Marq]

using Excel 2007


I'm looking for a formula that tells me how many times a value is repeated in a range of cells.

For example I have a range of cells , C1 to S5, that will SOMETIMES have a repeated number.

now in Cell C9 I will have one number, say number 27.

What would the the formula that I could put in C10 that tells me how many times the number 27 is in the range C1 to S5.......so for example if 27 was popping up 3 times in the C1 to S5 range, then in C10 the number three would be the result.


Now in C11 have a formula that tells me how many times "within one" of the number 27....so if there was a 26 or a 28 in the same cell range have that amount show up in C11....so lets say there were two 26's and three 28's....the value in C11 would be 5.....meaning 5 numbers are "within one" of 27

and finally in cell C12 have the same concept as within one...but this time set it as "within two" of the value of C9 (which is 27)....so this formula would only look for numbers within 2 of the number 27....so 29 and 25 would be the numbers i'm looking for...so however many numbers within two of 27 would pop up in C12.

man I hope I explained myself clearly....bacially one cell counts the how many numbers match C9....another cell counts how many numbers within one of C9...and last cell counts how many numbers within 2 of C9

 

[repairman615]

Hello CB12,

Sometimes it is easy to overlook something. I was not sure if maybe I had.

@Marq

Your PM about:

It's working but I'd like to include another scenario:

I would like to put in cell M39 to say the following:

If the value in M38 is greater than ZERO then leave M39 blank....BUT if the value of cell M38 has no value then COUNTIF($Z$1:$AC$6,M38-1)+COUNTIF($Z$1:$AC$6,M38+1),0) <!-- / message --><!-- sig -->
__________________
Marq
<!-- / sig -->

Try the below formula,
Cell M39:
<TABLE style="WIDTH: 477pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=636><COLGROUP><COL style="WIDTH: 477pt; mso-width-source: userset; mso-width-alt: 11629" width=636><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 477pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_ class=xl63 height=20 width=636>=IF(ISNUMBER(M38),"",COUNTIF($Z$1:$AC$6,M38-1)+COUNTIF($Z$1:$AC$6,M38+1))
<!-- / message --><!-- sig -->

</TD></TR></TBODY></TABLE>

 

[Marq]

Hello CB12,

Sometimes it is easy to overlook something. I was not sure if maybe I had.

@Marq

Your PM about:



Try the below formula,
Cell M39:
<TABLE style="WIDTH: 477pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=636><COLGROUP><COL style="WIDTH: 477pt; mso-width-source: userset; mso-width-alt: 11629" width=636><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 477pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_ class=xl63 height=20 width=636>=IF(ISNUMBER(M38),"",COUNTIF($Z$1:$AC$6,M38-1)+COUNTIF($Z$1:$AC$6,M38+1))
<!-- / message --><!-- sig -->

</TD></TR></TBODY></TABLE>

dang...still not working...ive checked all my formats...all of my rounding up or down...there not no decimals in the cells....everything seems as it should...the formauls are still not counting. I sincerely appreciate everyones help.

 

[repairman615]

See the other thread related to this, There is an updated formula that includes cell M15...see if that gets it.

http://www.mrexcel.com/forum/showthread.php?t=533922