# count last help

#### rmooretrans

##### New Member
=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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Von Pookie

##### MrExcel MVP
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

##### MrExcel MVP
Or is that:

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

#### rmooretrans

##### New Member
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

##### MrExcel MVP, Moderator
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

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

#### rmooretrans

##### New Member
=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

##### MrExcel MVP, Moderator
=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

##### New Member
Spot on. Thx. Much appreciated

Replies
10
Views
351
Replies
8
Views
187
Replies
10
Views
492
Replies
11
Views
396
Replies
6
Views
142

Threads
1,191,584
Messages
5,987,479
Members
440,097
Latest member
Wint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back