Count cells with positive values - advanced

[packie]

Hi,

I need a formula that will find the last three cells minus one cell in a range containing positive values only. The formula needs to be dynamic as more cells are added to the range.

Do let me know if you have difficulty understanding the question.

Thank you

packie

[#2]

@packie 

If the range ends in the column I, that could be

=COUNTIF(INDEX(A6:I6,1,COLUMN(I6)-3):INDEX(A6:I6,1,COLUMN(I6)-1),">0")

[#3]

Hi Sergei,

Nearly there, I have added Row6 and the forumla should now have moved on one cell with Row3,4&5 now the focus. That means Row2,3&4 are now not applicable.

[#4]

Is there anyone who can help me with this?

The formula provided needs to be dynamic to be of any use because new data is added parodically to the row of data.

[#5]

@packie 

=COUNTIF(INDEX(E6:ZA6,LARGE(IF(E6:ZA6<>"",COLUMN(E:ZA)-4),4)):INDEX(E6:ZA6,LARGE(IF(E6:ZA6<>"",COLUMN(E:ZA)-4),2)),">0")

This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

[#6]

@packie 

Another solution:

=LET(
    vector, TAKE(DROP(TOROW(6:6, 1), , -1), , -3),
    COUNT(LOG(vector))
)

[#7]

@Patrick2788 

Nice idea. Modified a bit if the last number is negative and we shall not to exclude it

=LET(
    v, TOROW($E6:$ZZ6,3),
    COUNT( LOG(
        TAKE( DROP(v,, @((TAKE(v,,-1)<0)-1)),, -3)
    ) )
)