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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓


    =COUNTIFS({Category Type}, "HVAC", {Date Opened}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!