After lots of googling I have found that the following formula does what I want it to do (I don't understand why COUNTIF has such a hatred of cells that aren't next to each other)

=SUM(COUNTIF(INDIRECT({"BC3","BE3","BK3"}),">"&AA3 ))



The trouble is now every time I add a column to my sheet it doesn't auto update the cell references, and I change this sheet all the time. Is the only way to avoid that to use the following, which is much more time consuming to write?

SUM(COUNTIF(BC6,...">"&AA6),COUNTIF(BE6,">"&AA6),C OUNTIF(BK6,">"&AA6)

I need loads of columns with formula like this and in reality the formula is counting in way more cells than shown here. Alternatively is there a statistical function that may help? AA3 is an average of all the other cells and I want to know how many of the original values were above the average.

Any suggestions would be massively appreciated.

Thank you.