Using a formula result to look for values in a range

[nightracer]

Hi

I have a range of results in A1:F12 as below, and what I want to do is rounddown the largest number to no decimal places, and then work out how many cells have a number equal or greater than that whole number.

So in A1:F12 I have:

130.293 131.782 126.154 132.584 126.015 129.799
131.086 131.595 124.231 130.213 123.110 128.141
130.365 130.063 121.647 128.116 122.565 129.057
132.887 133.115 126.164 133.080 126.137 129.236
131.868
133.256 133.962 126.391 133.001 126.605 131.345
130.945 130.391
128.354 129.803
129.698 129.730 123.366 130.680 123.593 129.812
129.883 130.151 123.997 130.665 123.509 129.667
130.664 130.543 123.882 131.163 124.462 129.599
130.371 130.073 123.148 128.525 121.408 128.680

In cell J1 I have a simple Rounddown(Large(A1:F12,1),0) formula (that returns 133 based on the above).
In cell K1 I want it to report the number of entries >= 133 (J1)

I have tried COUNTIF(A1:F12,">J1") but it doesn't seem to like reference to another cell and returns 0.
It should return 5

Any ideas of the way to do it?

Many thanks

 

[FDibbins]

Try this...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	130.293	131.782​	126.154​	132.584​	126.015​	129.799​		5​
2​	131.086	131.595​	124.231​	130.213​	123.11​	128.141​
3​	130.365	130.063​	121.647​	128.116​	122.565​	129.057​
4​	132.887	133.115​	126.164​	133.08​	126.137​	129.236​
5​	131.868
6​	133.256	133.962​	126.391​	133.001​	126.605​	131.345​
7​	130.945	130.391​
8​	128.354	129.803​
9​	129.698	129.73​	123.366​	130.68​	123.593​	129.812​
10​	129.883	130.151​	123.997​	130.665​	123.509​	129.667​
11​	130.664	130.543​	123.882​	131.163​	124.462​	129.599​
12​	130.371​	130.073​	123.148​	128.525​	121.408​	128.68​

H1=COUNTIF($A$1:$F$12,">="&INT(LARGE($A$1:$F$12,1)))

 

[liveinhope]

countif formula syntax is incorrect should be =COUNTIF(A1:F12,"> " & J1)

also if you want to count cells greater than or equal to formula should be =COUNTIF(A1:F12,">= " & J1) (The result will be different if any of the cells have a value of exactly 133)

 

[nightracer]

Thanks to both of you FDibbins and liveinhope, I thought it must be something relatively easy.

Appreciate it.
N

 

[FDibbins]

Happy to help