Need help with COUNTIFS in sheet summary utilizing 2 columns from the same sheet

I have a sheet with Start Date (date properties), which I have converted to Current Year (text/number properties) in a separate column and then another column called Fully Vaccinated? with dropdown single select properties. In the sheet summary, I have the following formula:

=COUNTIFS([Fully Vaccinated?]:[Fully Vaccinated?], "Yes", [Current Year]:[Current Year], "2022")

This formula returned values when initially created a week ago and when accessing yesterday, I'm receiving #INVALID DATA TYPE error message.

I would appreciate any advice or guidance on what I'm missing.

Thanks,

Sherry

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@Sherry Moran

What is the formula you are using to take the year from the date field?

You might also get an error if your Current Year or Start Date columns contain blanks.

You could also skip that whole extraction of the year if you want by putting the YEAR function inside your COUNTIFS:

=COUNTIFS([Fully Vaccinated?]:[Fully Vaccinated?], "Yes", [Start Date]:[Start Date], ISDATE(@cell), [Start Date]:[Start Date], YEAR(@cell) = "2022")

All comments

Jeff Reisman

@Sherry Moran

What is the formula you are using to take the year from the date field?

You might also get an error if your Current Year or Start Date columns contain blanks.

You could also skip that whole extraction of the year if you want by putting the YEAR function inside your COUNTIFS:

=COUNTIFS([Fully Vaccinated?]:[Fully Vaccinated?], "Yes", [Start Date]:[Start Date], ISDATE(@cell), [Start Date]:[Start Date], YEAR(@cell) = "2022")

Sherry Moran

@Jeff Reisman

Thanks so much for your help - yes, this worked.

I used =YEAR([Start Date]@row) to convert to the year, but what you've suggested works for what I need. Appreciate the help!

Sherry Moran

Jeff Reisman

@Sherry Moran

Glad it worked.

For those keeping score at home:

Formulas in Smartsheet work from left to right. The [Start Date]:[Start Date], ISDATE(@cell) part of the formula eliminates from consideration any fields from the Start Date column that aren't a date value. It's set to the left of the [Start Date]:[Start Date], YEAR(@cell) = "2022" part, so that when then formula then evaluates for the YEAR value, it's only looking at cells that have a date value in them; thus, it does not error.

If I were to switch the order, it would fail:

Here it works:

Here it does not:

Sherry Moran

@Jeff Reisman

Thanks for the explanation. Awesome 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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.