COUNTIFS Reference Another Sheet, Category Type and Date Range

John Hageman

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.

Mark Cronk

Try:

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

Mark

Mark Cronk

Try:

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

Mark

John Hageman

https://community.smartsheet.com/discussion/comment/279723#Comment_279723

That's it! Thanks, Mark!

Mark Cronk

Glad you found a solution. Thank you for contributing to the the Community.

Mark