# Accurately Count if Multiple Contacts are in a Cell

✭✭

Hi,

I am really struggling trying to map out certain data points, when it involves our sales reps, because we can have multiple contacts in a single cell.

In this particular instance, I want to find out how many quotes a sales rep has issued this year. Problem is, multiple sales reps can quote the same job, so another contact name could also be in this cell.

Here is the formula: =COUNTIFS({Bid Date}, >=DATE(2022, 12, 31), {Bid Date}, <=DATE(2023, 12, 31), {Phase}, ="Quote", {Outside Sales Rep}, =([Sales Engineer]@row))

The problem is: This only counts it if they are the ONLY sales rep in the row.

Smartsheets is telling me to use the "FIND" function, but no matter how I have set this up in the formula, it doesn't work. Any words of advice or a fix to my formula?

Tags:

• ✭✭✭✭✭✭

Try this:

=COUNTIFS({Bid Date}, >=DATE(2022, 12, 31), {Bid Date}, <=DATE(2023, 12, 31), {Phase}, ="Quote", {Outside Sales Rep}, HAS(@cell, [Sales Engineer]@row))

• ✭✭
edited 03/28/23

@Paul Newcome I thought it worked, but when I went to check its accuracy, my reference sheet has 32 instances but the formula only pull sup 20 for this particular sales person.

• ✭✭✭✭✭✭

Are you able to provide screenshots? What is the exact formula you are using, and can you provide a screenshot of the filter being used?

• ✭✭

Formula:

=COUNTIFS({Bid Date}, >=DATE(2022, 12, 31), {Bid Date}, <=DATE(2023, 12, 31), {Phase}, ="Quote", {OSR}, HAS(@cell, [Sales Engineer]@row))

@Paul Newcome

• ✭✭✭✭✭✭

The sheet filter does not include the date range/criteria. What happens if you include that in the filter?

• ✭✭

That was it! Thank you!

This was definitely a case of me looking at the same equation for WAY tooooooo long. I needed your fresh eyes.

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!