countif function or sumif

Originally Posted by tipuser

I have number 1 in cell A3.
In range A6:A5000 I have number 1 many times between empty cells
How can I have in cell A4 the number of empty cells/rows since the last #1.
How can I have in cell A5 the average number of times number 1 appeared in the range, and finally,
Highlight A4 or A6 if the value in A4 is higher than the average (A5).

This will help a lot. Thanks

Try this in A4

=MAX(IF(ISBLANK(A6:A5000), ROW(A6:A5000),0))-MAX(IF(NOT(ISBLANK(A6:A5000)), ROW(A6:A5000),0)) enter this with Ctrl+Shift+Enter

Then in A5

=COUNTIF(A6:A5000,1)

Then set condition formatting on A4

Cell value > greater than > A5 then pick the format you require

OK, misunderstood your question, try this with the help of another cell, put this in B4

=ADDRESS(MAX(NOT(ISBLANK(A6:A5000))*ROW(A6:A5000)),1) but enter with Ctrl+Shift+Enter

then in B3

=MID(C4,3+1,4)-6 this will give you the next blank cell after the last number 1

or you could try this for the row number of the next empty cell

=MID(C4,3+1,4)+1