+ Reply to Thread
Results 1 to 4 of 4

Count last 10 occurrences of a word in a variable range

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    brazil
    MS-Off Ver
    Excel 2007
    Posts
    64

    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
    Last edited by dualaudio454252; 06-27-2016 at 04:17 PM. Reason: I couldn't attach a file [U]by any way[/U] to the forum.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,265

    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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-19-2013
    Location
    brazil
    MS-Off Ver
    Excel 2007
    Posts
    64

    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.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-19-2013
    Location
    brazil
    MS-Off Ver
    Excel 2007
    Posts
    64

    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!
    Attached Files Attached Files
    Last edited by dualaudio454252; 06-28-2016 at 08:47 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count # of occurrences in range
    By HalPlz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2015, 02:28 AM
  2. [SOLVED] Count the occurrences of 3 words in a range
    By tlacloche in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-20-2014, 10:54 AM
  3. Replies: 0
    Last Post: 07-28-2013, 10:24 AM
  4. [SOLVED] Count occurrences of text in a range
    By braydon16 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-18-2013, 08:33 PM
  5. [SOLVED] Count Word Occurrences from a different tab
    By Jaypelt in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-04-2013, 02:48 PM
  6. Excel 2007 : Count Occurrences of Text in Data Range
    By gtlandis in forum Excel General
    Replies: 5
    Last Post: 09-22-2011, 11:42 PM
  7. Formula to count number of occurrences of a word, with some additional conditions
    By mindmystique in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2011, 05:29 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1