# Include IF statement in most frequent text formula

1. ## 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. ## 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. ## Re: Include IF statement in most frequent text formula

AWESOME this is exactly what I was trying to do.

4. ## 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. ## 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.

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.

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

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