formula advise please

Si Spence

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!

Thank you in advance,  Si.

SmSulli

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