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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!