Cross Sheet Reference COUNTIFS
Hi there,
I'm fairly new to Smartsheets so I apologize if there's an obvious answer to this question, but here's my situation.
I have a sheet that tracks projects and their status. Along with the status column, there's another column that auto-populates the date a project is marked as "Completed". I want to create a metric that pulls the number of projects completed in the current year. In my metrics sheet, I'm trying to create a COUNTIFS formula with two cross sheet references. {Intake Status} references the status column, {Status Status Date} references the date column.
Here's my formula:
=COUNTIFS({Intake Status}, "Completed", YEAR({Intake Status Date}), YEAR(TODAY()))
The value pulled from [Primary Column]@row is "Completed".
The result I get is #INVALID DATA TYPE
If I only include the first reference it works fine so the problem is in the second range/criterion.
Any advice would be appreciated.
Thanks.
Bart
Best Answers
-
Try this:
=COUNTIFS({Intake Status}, "Completed", {Intake Status Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
-
That worked! Thanks Paul.
Answers
-
Try this:
=COUNTIFS({Intake Status}, "Completed", {Intake Status Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
-
That worked! Thanks Paul.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!