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

Options

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

«1

• ✭✭✭✭✭
edited 09/04/20
Options

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!

• edited 09/04/20
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• Options

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

• edited 09/11/20
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

It's a multi-select contact list

• Options

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

• Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!