Accurately Count if Multiple Contacts are in a Cell

MShultz

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?

Paul Newcome

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

MShultz

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

Paul Newcome

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?

MShultz

Formula:

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

@Paul Newcome

Paul Newcome

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

MShultz

@Paul Newcome

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.

Paul Newcome

Happy to help. 👍️