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
Best Answer

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")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

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")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

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:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thanks for the explanation. Awesome help!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!