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

 

[doofusboy]

=countif(c1:s5,c9)

 

[Dr Dee]

Marg,
I'm no expert at this (ask anyone)........um, but I was gonna say what doofusboy just said (****, he beat me to it)...
Ya gotta be quick 'round here.....

 

[repairman615]

<TABLE style="WIDTH: 311pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=414><COLGROUP><COL style="WIDTH: 311pt; mso-width-source: userset; mso-width-alt: 7570" width=414><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 311pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=414>27</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>=COUNTIF(C1:S1,C8)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>=COUNTIFS(C1:S1,">="&C8-1,C1:S1,"<="&C8+1)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>=COUNTIFS(C1:S1,">="&C8-2,C1:S1,"<="&C8+2)</TD></TR></TBODY></TABLE>


Here uses cell C8 to house the number 27.

 

[repairman615]

I just seen the cell Ref. needs to be C9:

Excel Workbook
	C
9	27
10	=COUNTIF(C1:S1,C9)
11	=COUNTIFS(C1:S1,">="&C9-1,C1:S1,"<="&C9+1)
12	=COUNTIFS(C1:S1,">="&C9-2,C1:S1,"<="&C9+2)
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C10	=COUNTIF(C1:S1,C9)
C11	=COUNTIFS(C1:S1,">="&C9-1,C1:S1,"<="&C9+1)
C12	=COUNTIFS(C1:S1,">="&C9-2,C1:S1,"<="&C9+2)

 

[Marq]

I just seen the cell Ref. needs to be C9:

Excel Workbook
	C
9	27
10	=COUNTIF(C1:S1,C9)
11	=COUNTIFS(C1:S1,">="&C9-1,C1:S1,"<="&C9+1)
12	=COUNTIFS(C1:S1,">="&C9-2,C1:S1,"<="&C9+2)
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C10	=COUNTIF(C1:S1,C9)
C11	=COUNTIFS(C1:S1,">="&C9-1,C1:S1,"<="&C9+1)
C12	=COUNTIFS(C1:S1,">="&C9-2,C1:S1,"<="&C9+2)

works perfect!..thanks!

 

[repairman615]

This should work for numbers that are not within 2 but 2 greater or less than your target number.
Per your PM:

Excel Workbook
	C
9	27
10	5
11	4
12	4
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C10	=COUNTIF(C1:S1,C9)
C11	=COUNTIF(C1:S1,C9+1)+COUNTIF(C1:S1,C9-1)
C12	=COUNTIF(C1:S1,C9+2)+COUNTIF(C1:S1,C9-2)

 

[cb12]

Surely having these formulas within the searchable range will eventually lead to a circular reference?

 

[repairman615]

Surely having these formulas within the searchable range will eventually lead to a circular reference?

The range is C1:S1, the formulas are in cells C10 and C11.

So in other words, row1 has the range, row10 and 11 have the formulas.

I do not understand what you are seeing.

 

[cb12]

Apologies I misread the range as C1:S50. My mistake