+ Reply to Thread
Results 1 to 5 of 5

Include IF statement in most frequent text formula

  1. #1
    Registered User
    Join Date
    11-04-2009
    Location
    Ann Arbor, MI
    MS-Off Ver
    Excel 2003
    Posts
    2

    Include IF statement in most frequent text formula

    Hi,

    I have one column of sample numbers and another column of tree species (text values). There are a lot of trees for each sample number. I need to find the most common tree species for each sample number. So I have this formula to find the most frequently occurring tree species for all samples:

    =INDEX(B:B,MATCH(MAX(COUNTIF(B:B,B:B)),COUNTIF(B:B,B:B),0))

    and that works. Is there a way to modify the formula to find the most frequent text value for only those rows of B where the value of column A equals 1,2,3, etc?

    Thank you!!

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Include IF statement in most frequent text formula

    Try a formula like this

    =INDEX(B1:B100,MODE(IF(A1:A100=2,IF(B1:B100<>"",MATCH(B1:B100,B1:B100,0)))))

    confirmed with CTRL+SHIFT+ENTER

    You need to use a specific range rather than the whole column....

  3. #3
    Registered User
    Join Date
    11-04-2009
    Location
    Ann Arbor, MI
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Include IF statement in most frequent text formula

    AWESOME this is exactly what I was trying to do.

  4. #4
    Registered User
    Join Date
    12-21-2006
    MS-Off Ver
    Office 2013
    Posts
    73

    Re: Include IF statement in most frequent text formula

    I am trying to do the same thing but the check data is text. Is there another way to write the formula to work with text since 'mode' is only for numbers?

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

    Re: Include IF statement in most frequent text formula

    enphynity...
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    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 some-1 helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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