How do I get Countif, Find, and Contains in one formula to work?

Krystal Vaughn

In my pipelines metrics sheet I am trying to get a formula to find the external account holders name along with closed won, for a count of how many that account holder has closed a win. This is one equation i am trying an dI am not getting it to work to count both.....any help would be greatly appreciated

=COUNTIF({Sheet - Sales Pipeline Range 1}, FIND("External Account Holder", @cell) > 0, CONTAINS(Closed Won, @row))

Paul Newcome

Happy to help. 👍️

Paul Newcome

Multi-Select Contact columns can be a bit tricky. Lets try this...

=COUNTIFS({MSS Sheet - MSS Account Owner}, AND(FIND("Team Member 1", @cell) > 0, FIND("Team Member 2", @cell) > 0, LEN(@cell) = LEN("Team Member 1" + "Team Member 2") + 2))

Paul Newcome

Happy to help. 👍️

Basically (based on my testing) it looks like multi-select contact columns put two characters between contacts. This is weird though because if you search for spaces using something like a FIND function it will only locate 1 (immediately after the 1st contact). I haven't been able to figure out what the second character is though (a little more testing should reveal that), but most of that is besides the point for this particular solution.

All we really need to know is that there are two characters. So we use the LEN function to find out how many characters are in the two names then add two. Then we can say that if that number equals the number of characters in the cell along with the two names being found then we can confirm/count that that particular cell has both of those names and no others.

David Joyeuse

Hi @Krystal Vaughn

You miss the range for the Closed Won criteria between your FIND and CONTAINS functions.

edit: nearly forgot that you should use COUNTIFS instead of COUNTIF. And if that's the same range for both criteria, use an AND function prior to FIND :)

Hope it helped!

Krystal Vaughn

Its the same sheet I am pulling the information from into the metrics sheet but I need it to count the external account holders name only if a seperate row states "closed won", if that makes sense @David Joyeuse

Paul Newcome

Try something like this...

=COUNTIFS({Sheet - Sales Pipeline Range 1}, FIND("External Account Holder", @cell) > 0, {Sheet - Sales Pipeline Status Column}, CONTAINS("Closed Won", @cell))

Krystal Vaughn

@David Joyeuse It worked, thanks for your help with the "countifs". Here is the final formula that is working smoothly now! Have a great Holiday Weekened!😊

=COUNTIFS({MSS Sheet - Sales Pipeline Range 2}, FIND("External Account Holder", @cell) > 0, {Sheet - Sales Pipeline Range 4}, "Closed Won")

Krystal Vaughn

@Paul Newcome Thank you so much! That worked, except I didn't have the @cell in "closed one". You are good at these! Thanks so much for your help! I am slowly getting there!

Paul Newcome

Happy to help. 👍️

Krystal Vaughn

@Paul Newcome @David Joyeuse Good morning! I hate to be a bother but I am trying to do the same thing but only pull a count for the team(s) that worked together overall, not with a "closed won" and I have tried everything I feel like. If it was only a team of two I want that to pull and not if they worked together on other teams, say a team of 3 and there names appear in a cell together. Is there a way to do that?

This is my formula but it pulls their names in all cells that they appear together not just 2 on there own and gives me "3"

=COUNTIFS({MSS Sheet - MSS Account Owner}, AND(FIND("Team Member 1", @cell) > 0, FIND("Team Member 2", @cell) > 0)

I have also tried this and am still not getting it

=COUNTIFS({MSS Sheet - MSS Account Owner}, "Team Member 1", {MSS Sheet - MSS Account Owner}, "Team Member 2")

and it gives me "0" even though they have been a team just them together twice

Any help is greatly appreciated!

Paul Newcome

Are you using a Contact type column or a Multi-Select type column for this one?

Krystal Vaughn

It's a multi-select contact list

Krystal Vaughn

It counts it fine for teams of 3 with this formula

=COUNTIFS({MSS Sheet - MSS Account Owner}, AND(FIND("Team Member 1", @cell) > 0, FIND("Team Member 2", @cell) > 0, FIND("Team Member 3", @cell) > 0))

Paul Newcome

Multi-Select Contact columns can be a bit tricky. Lets try this...

=COUNTIFS({MSS Sheet - MSS Account Owner}, AND(FIND("Team Member 1", @cell) > 0, FIND("Team Member 2", @cell) > 0, LEN(@cell) = LEN("Team Member 1" + "Team Member 2") + 2))

Krystal Vaughn

Holy cow, you are amazing! That worked! That is a complex formula, wow! I would have been trying forever!! Thanks again so much!! @Paul Newcome

Paul Newcome

Happy to help. 👍️

Basically (based on my testing) it looks like multi-select contact columns put two characters between contacts. This is weird though because if you search for spaces using something like a FIND function it will only locate 1 (immediately after the 1st contact). I haven't been able to figure out what the second character is though (a little more testing should reveal that), but most of that is besides the point for this particular solution.

All we really need to know is that there are two characters. So we use the LEN function to find out how many characters are in the two names then add two. Then we can say that if that number equals the number of characters in the cell along with the two names being found then we can confirm/count that that particular cell has both of those names and no others.

Krystal Vaughn

Ohh that makes sense, genius....I didn't even think to count the characters or use LEN. I just learned so much from that one formula and you.

Paul Newcome

I'm glad it makes sense and that you were able to learn from it. I learn something new everyday and absolutely love it.