How to link cells as criteria for CountIF function

Occasional Visitor


Briefly, I have 314 lines of data with each line containing ~30 data points. For my purposes I want to identify any data points within each line that are considered "Hits" by whether or not they are above a certain threshold calculated at the top of each line (3 standard deviations above the mean).


My first approach is to use the CountIF function and set criteria  to ">ValueX" and just manually pick from the lines which have any eligible data points. Ideally I could just set the criteria as the top cell (containing the threshold value) and drag it across the 314 lines/columns. However it seems excel does not allow this. The alternative is to manually type in the value for each CountIF function (shown in attached screenshot). but I will likely have to go back and determine "hits" with different calculations for threshold values and this approach would become time consuming. 


If anyone has recommendations or tricks to bypass this issue, or a more practical approach it would be greatly appreciated. There should not be a large number of data points which result as hits, so an approach that uses conditional formatting to highlight cells above the threshold value would work also.





Screen Shot 2022-05-31 at 4.57.39 PM.png



1 Reply


In stead of typing ">2.99" use ">"&F2

Now you can copy the formula across columns. F2 will become G2, H2 etc. when copied towards the right. And E2, D2 etc. when copied to the left.