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

SergeiBaklan

MVP

Mar 31, 2020

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

Countif formula for three positive cells.xlsx18 KB

packie
Brass Contributor
Mar 31, 2020
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.
- packie
  Brass Contributor
  Apr 01, 2020
  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.
  - Patrick2788
    Silver Contributor
    May 13, 2024
    packie
    Another solution:
    
    =LET( vector, TAKE(DROP(TOROW(6:6, 1), , -1), , -3), COUNT(LOG(vector)) )

Forum Discussion

Count cells with positive values - advanced

Share

Resources