COUNTIFS Reference Another Sheet, Category Type and Date Range
Ask for help:
I have a master sheet with all of the data that I am trying to pull from. In this sheet, I have already achieved the desired outcome in the 'Sheet Summary'.
Explanation Summary: Count issues by categories types within Q1
Formula in 'Sheet Summary':
=COUNTIFS([Category Type]:[Category Type], "HVAC", [Date Opened]:[Date Opened], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))
I have a separate 'Grid' Sheet. I want to use this formula and make changes to it to meet the criteria for the Grid Sheet (see image).
I know I have to Reference Another Sheet. I setup the columns as references in the Reference Manager.
Now, I am getting errors when trying to use the formula below.
Formula in 'Grid' Sheet:
=COUNTIFS({Category Type}, "HVAC", {Date Opened}, @cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31))
I am getting the #INVALID OPERATION Error.
Thanks for your help.
Best Answer
-
Try:
=COUNTIFS({Category Type}, "HVAC", {Date Opened}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Try:
=COUNTIFS({Category Type}, "HVAC", {Date Opened}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
That's it! Thanks, Mark!
-
Glad you found a solution. Thank you for contributing to the the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!