COUNTIFs referencing 1 dropdown column and 1 date column
I am using the formula below to find the number of times a particular service was requested in a given year. I am referencing columns from a sheet called Sheet1.
=COUNTIFS({Sheet1 Service Requested}, "Isolate Requested", {Sheet1 Date of Completion}, YEAR(@cell ) = 2021)
The column Service Requested is a dropdown with Isolate Requested as 1 item in the list.
The column Date of Completion is a date column with dates like 1/3/24.
The formula is returning zero results, but I know that there are at least 11 entries that meet these criteria.
I am able to get the number of Service Requested = "Isolate Requested" using COUNTIF like this:
=COUNTIF({MOAC Intake Service Requested}, "Isolate Request")
So I know SmartSheet can count things in a dropdown list and suspect I'm doing something wrong with the date portion of the formula.
Appreciate any help!
Answers

@HSH your formula looks correct to me and I tested it on a sheet and it worked fine.
I see you are using two different references  {Sheet1 Services Requested} and {MOAC Intake Service Requested} and also two different criteria ("Isolate Request" vs. "Isolate Requested"). If your second formula is working I'd guess that the criteria you're looking for in your first formula is "Isolate Request" and it'll work fine as long as the references point to the right columns. The Date portion of your first formula is correct.

Thank you for the feedback. I'm actually working with 2 columns from 1 sheet (a typo caused the difference). I was just able get the formula working by putting the date criteria first. Not sure that really matters, but things are working now. Your feedback on the date formula is much appreciated!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!