Count cells with positive values - advanced

Question

Hi,&nbsp;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.&nbsp;Do let me know if you have difficulty understanding the question.&nbsp;Thank youpackie

patrick2788 · Answer

packie&nbsp;Another solution:&nbsp;=LET(
    vector, TAKE(DROP(TOROW(6:6, 1), , -1), , -3),
    COUNT(LOG(vector))
)

sergeibaklan · Answer

Patrick2788&nbsp;
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)&lt;0)-1)),, -3)
    ) )
)

sergeibaklan · Answer

packie&nbsp;
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),"&gt;0")

packie · Answer

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

packie · Answer

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.

Forum Discussion

Count cells with positive values - advanced

Share

Resources