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

Kari G

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! 🤯😰🤪

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.