Count last 10 occurrences of a word in a variable range

1. Count last 10 occurrences of a word in a variable range

Please, I need an array formula to count how many times ""CAN" appears in the last 10(AD3) words in column G, until the row of 30(AD2) in column A, without a helper column (G:G).

I must keep cells AD2 and AD3 (or 2 others) as references.

I've tried with a helper colum, but it's too heavy to calculate, cause the actual sheet has thousands of rows and runs a macro.

See attached file.

Sorry, I couldn't attach a file by any way to the forum.
http://www.filedropper.com/canfiletoexcelforum

2. Re: Count last 10 occurrences of a word in a variable range

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

3. Re: Count last 10 occurrences of a word in a variable range

Thank you very much, FDibbins!

I have the file uploaded now.

I've changed the description to make it more clear.

I need an array formula to count how many times "CAN" appears in the last 10(AD3) words in column F:F, until the row of 30(AD2) be matched in column A, WITHOUT a helper column (G:G).

It's not between rows 10 and 30, it's to count "CAN" between the last 10 words before line 30, ignoring blanks, right into column F:F.

I must delete column G:G and keep cells AD2 and AD3 (or others) as references.

I've tried with a helper colum, but it's too heavy to calculate, cause the actual sheet has thousands of rows and runs a macro.

4. Re: Count last 10 occurrences of a word in a variable range

Thanks to the unexpected and GREAT HELP of GC Excel, I adapted the solution he provided to get the actual working file, that is the attached file number 3.

Thank you again, FDibbins, for your attention.

Thank you VERY, VERY MUCH, GC Excel!

There are currently 1 users browsing this thread. (0 members and 1 guests)