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.

Do you mean that AA3 is the average of a continuous range like A1:G1 ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

No I'm afraid not.

Here is one suggestion.

Attached Files

CountNonContiguous.xlsx (10.3 KB, 14 views)

Thank you xor. I don't fully understand the criteria that decides if a cell gets taken and put into the continuous range (I have only recently learnt how to use INDEX) so I will experiment with it when I get home from work.

Thanks again for your help.

It takes up to and including the last cell in BC3:BZ3 containing a number. You can change BZ3 to a higher address if needed.

Please note that the formulas in BC1:BZ1 are array formulas. An array formula must be entered by holding down Ctrl and Shift before pressing Enter where you normally just press Enter. If you do it correct Excel will automatically put braces {} around the formula. When you have entered one array formula correctly you can copy it to other cells in the normal way.

On second thought I don't think my suggestion works. I guess you have something in between BC3 and BE3, BE3 and BK3 and so on?

Use a namedefinition for de used cells.