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!