How do I get Countif, Find, and Contains in one formula to work?
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))
Best Answers

Happy to help. 👍️

MultiSelect 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))

Happy to help. 👍️
Basically (based on my testing) it looks like multiselect 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.
Answers

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!

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

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

@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")

@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!

Happy to help. 👍️

@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!

Are you using a Contact type column or a MultiSelect type column for this one?

It's a multiselect contact list

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

MultiSelect 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))

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

Happy to help. 👍️
Basically (based on my testing) it looks like multiselect 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.

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.

I'm glad it makes sense and that you were able to learn from it. I learn something new everyday and absolutely love it.
Help Article Resources
Categories
Check out the Formula Handbook template!