#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

✭✭✭✭✭✭
edited 04/25/17

hiya,  please see screen shots.  I have a list of sales people assigned to target new customers.  I want to know how many of their target customers are open opportunities (taken from a dropdown)  but my summary table is picking up all the companies that have been contacted but are not suitable for any reason.  i just need a 'count' formula that looks at the sales people and tells me how many open opportunities they have on their list by looking at all 'prospect' options below.

I then need to bring this data into a Sight...but that's another job!

• Give this a try.

For "Open Opportunities" you can add up all the "Prospect..." items with a Countif

=COUNTIF([Account Status]8:[Account Status]12, "Prospect Not Yet Contacted") + COUNTIF([Account Status]8:[Account Status]12, "Prospect On Hold") + COUNTIF([Account Status]8:[Account Status]12, "Prospect Work In Progress")

For each individual Account Manager its more complicated, but you can use a Countifs

=COUNTIFS([Account Manager]8:[Account Manager]12, [Account Manager]3, [Account Status]8:[Account Status]12, "Prospect Not Yet Contacted") + COUNTIFS([Account Manager]8:[Account Manager]12, [Account Manager]3, [Account Status]8:[Account Status]12, "Prospect On Hold") + COUNTIFS([Account Manager]8:[Account Manager]12, [Account Manager]3, [Account Status]8:[Account Status]12, "Prospect Work In Progress")

I included a picture of my trial sheet.

Good Luck

This discussion has been closed.