Sum of last 10 cells to contain partial text

[JBeau16]

I am looking to find the number of cells in the last 10 occurrences that contain a specific text, but can't seem to get the formulas correct to include the criteria of the partial text.

Below is the list of "results" which I add to row by row as they occur. I am looking to input in a different cell the number of cells that contain "W" from the last 10 rows that aren't blank. The formula below is as far as I can figure.

=SUM(TAKE(FILTER(H3:H84,H3:H84<>0),-10))

Result

W

SOW

L

W

L

L

L

OTW

W

W

L

OTW

OTW

L

W

L

W

W

L

W

W

OTL

W

L

L

L

L

W

W

OTL

SOL

W

SOL

L

W

W

W

W

W

SOL

W

OTW

L

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(a,TAKE(FILTER(H3:H84,H3:H84<>0),-10),ROWS(FILTER(a,ISNUMBER(FIND("w",a)))))

 

[JBeau16]

Hi & welcome to MrExcel.
How about

=LET(a,TAKE(FILTER(H3:H84,H3:H84<>0),-10),ROWS(FILTER(a,ISNUMBER(FIND("w",a)))))

Still not working, I believe it isn't converting the value of the cell containing "w" to a number, so it is coming back with #CALC!

 

[Fluff]

Oops, it should be search & not find

=LET(a,TAKE(FILTER(H3:H84,H3:H84<>""),-10),ROWS(FILTER(a,ISNUMBER(SEARCH("w",a)))))

 

[JBeau16]

Oops, it should be search & not find

=LET(a,TAKE(FILTER(H3:H84,H3:H84<>""),-10),ROWS(FILTER(a,ISNUMBER(SEARCH("w",a)))))

That worked for the "w", which is great, thank you!

Now if I am looking to do the same for just the cell's with the value of L without including the SOL or OTL how would I do that?

 

[Fluff]

How about

=LET(a,TAKE(FILTER(H3:H84,H3:H84<>0),-10),ROWS(FILTER(a,a="L")))

 

[JBeau16]

How about

=LET(a,TAKE(FILTER(H3:H84,H3:H84<>0),-10),ROWS(FILTER(a,a="L")))

That did the trick, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JBeau16]

I am using this formula to count the number of results in the last 10 rows that include "ol", but as of right now there are none in the last 10 rows, so the cell is coming up with #CALC!

I understand that I need to add a 0 for the "if empty" into the FILTER, but when I do it changes the result to 1, which is incorrect. Any thoughts?

=LET(a,TAKE(FILTER(H3:H84,H3:H84<>""),-10),ROWS(FILTER(a,ISNUMBER(SEARCH("ol",a)))))

 

[Fluff]

You need to use

=LET(a,TAKE(FILTER(H3:H84,H3:H84<>""),-10),iferror(ROWS(FILTER(a,ISNUMBER(SEARCH("ol",a)))),""))