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.

Find more posts tagged with

Formulas

Accepted answers

Mark Cronk

Try:

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

Mark

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.