count last help

[rmooretrans]

=COUNT(INDEX(O3:O35,LARGE(IF(O3:O35,"w*",ROW(O3:O35)-ROW(O3)+1),MIN(10,COUNT(O3:O35)))):O35)
trying to only count last 10 in this column but only the ones with "w" in them in no "w" then dont count

thx in advance

 

[Von Pookie]

I'm a little confused--if you know the range (O3:O35) and only want to count the last 10 cells (which would seem to be O26:O35), why not just reference them? That seems an awfully complex formula for something that could be as simple as =COUNTIF(O26:O35,"*w*")

 

[Richard Schollar]

Or is that:

=MIN(10,COUNTIF(O3:O35,"*w*"))

 

[rmooretrans]

sorry for the confusion. I trying to count the last 10 cells of a given range that contain info such as "w, #-#" from say a1:a33 however only give a number that corresponds to the ones containing a "w" of the last 10. Also, a1:a33 doesnt always contain data...in other words a1 through a8 may have data, so in this instance i would want a running count of all 8 that contain a "w". hope this makes sense. thx.

 

[Peter_SSs]

Assuming data starts in O3 and there are no gaps in the data, see if either of these suit.

=COUNTIF(OFFSET(O3,COUNTA(O3:O35)-1,0,-MIN(10,COUNTA(O3:O35))),"*w*")

=COUNTIF(INDEX(O3:O35,MAX(1,COUNTA(O3:O35)-9)):INDEX(O3:O35,COUNTA(O3:O35)),"*w*")

 

[rmooretrans]

Perfect exactly what i needed. Looks like they both produce the same results. thx

 

[rmooretrans]

=AVERAGE(INDEX(AI54:AI82,LARGE(IF(AI54:AI82<>"",ROW(AI54:AI82)-ROW(AI54)+1),MIN(10,COUNT(AI54:AI82)))):AI82)

need help tweaking this formula to ignore N/A last cell and move up to next cell that contains a number. The AI cell it references contains formulas imports a number from alternate sheet if available if not it puts N/A. Appreciate the help. thx.

 

[Peter_SSs]

=AVERAGE(INDEX(AI54:AI82,LARGE(IF(AI54:AI82<>"",ROW(AI54:AI82)-ROW(AI54)+1),MIN(10,COUNT(AI54:AI82)))):AI82)

need help tweaking this formula to ignore N/A last cell and move up to next cell that contains a number. The AI cell it references contains formulas imports a number from alternate sheet if available if not it puts N/A. Appreciate the help. thx.

Using similar ideas & assumptions as before, do these work?

=AVERAGE(OFFSET(AI54,COUNT(AI54:AI82)-1,0,-MIN(10,COUNT(AI54:AI82))))

=AVERAGE(INDEX(AI54:AI82,MAX(1,COUNT(AI54:AI82)-9)):INDEX(AI54:AI82,COUNT(AI54:AI82)))

 

[rmooretrans]

Spot on. Thx. Much appreciated