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
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!