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?

Find more posts tagged with

Contacts

Formulas

Accepted answers

All comments

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

Screen Shot 2023-03-29 at 8.59.27 AM.png

Screen Shot 2023-03-29 at 8.59.38 AM.png

Screen Shot 2023-03-29 at 9.00.09 AM.png

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.