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!

Find more posts tagged with

Accepted answers

All comments

ericncarr

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

HSH

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!

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.