Forum Discussion

Brass Contributor

Mar 31, 2020

Count cells with positive values - advanced

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 le...

Countif formula for three positive cells.xlsx11 KB

Formulas and Functions

Patrick2788

Silver Contributor

May 13, 2024

packie

Another solution:

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

SergeiBaklan

Diamond Contributor

May 13, 2024

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)
    ) )
)