How to count specific records from a "Lookup" field

[Dereck_Hoekstra]

I’m trying to count specific records that appear in a “Lookup” field. The problem with applying a “Count” field to the “Lookup” field is it counts ALL the linked records that appear in that lookup field.

Lets say the Lookup field is pulling the names of multiple people from Table A into Table B. In one of the records of Table B, lets say the following values appear in the Lookup field.

• Brooke H
• Brooke H
• Brooke H
• John M
• Ronny W
• Ronny W

I want to apply a formula to the Lookup field that counts the amount of times “Brooke H” appears. With the above data, the formula should return the value of “3”, since Brooke H is listed 3 times. If I were to apply a “Count” field to this, it would give me a value of “6”, counting all the “records” that appear in the Lookup field. I want to figure out how to count specific values, not total records. Any tips or suggestions in much appreciated!

[Justin_Barrett]

Add two fields next to your Lookup field. Name one of them something like “Find”, which represents the thing you’re looking for. Make the second field a formula:

(LEN(Lookup & "") - LEN(SUBSTITUTE(Lookup & "", Find, ""))) / LEN(Find)

What this does is compare the length difference between the full mashed-together lookup content, and that same mashed-together content with all of your to-be-found text removed. Divide that by the length of the text you’re seeking, and the result is the number of times that thing was in the lookup. As long as what you put into the “Find” field is unique—i.e. it can’t be a subset of something longer, like Ron vs Ronny—this will work.