COUNTIFS formula not calculating correctly in 3 of 4 columns- I'm at a loss

What is wrong with my formula? I need it to return the number of times it finds a certain answer from a drop down list for a specific facility between 2 dates. The dates we are searching are from the first of the current month through yesterday. Ex. if it's March 13, 2024, I'm looking for data between March 1, 2024-March 12, 2024. I am referring across sheets.

This is my current formula

=COUNTIFS({PT Sat Surv Range 1 - Facility Name}, [Primary Column]@row, {Patient Satisfaction Survey Reason}, "Yes", {PT Sat Surv Range 1 - Date Created}, AND(MONTH(@cell) = MONTH(TODAY()), YEAR(@cell) = YEAR(TODAY()), @cell < TODAY()))

I'm not getting an error message, I'm getting an incorrect number, sometimes too high and sometimes too low. We have been working on this for weeks.

HELP! 🤯😰🤪

Find more posts tagged with

Formulas

Cell linking

Accepted answers

All comments

KPH

Is your drop down list a multi-select or single select? If it is multi-select you would need to use a HAS function in your second criteria.

If that isn't the issue, are you able to share a screenshot of your data?

Kari G

https://community.smartsheet.com/discussion/comment/425191#Comment_425191

It is a single select drop down list. I can share the data column that the formula is based on, "Was the Survey presented to the patient?". I clicked on the drop down so you could see the 4 options.

Screenshot of Data 3.14.24.png

KPH

And you are expecting the count to be just those who selected "Yes", and not include those that selected "Yes - but patient declined"?

Kari G

https://community.smartsheet.com/discussion/comment/425337#Comment_425337

Correct, I have made sure they are not adding together. The number that that the formula is producing never adds up, where it would have both options included.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.