How to correctly refer to another sheet for COUNTIF?

love_ss_2023

Hello,

I am new to smartsheet and trying to create a dashboard for my source sheet. I have created a blank sheet to help add all my formulas.

1. I have a source sheet - "MySheet"

2. It has a "Category" column and is a multi-select dropdown list

3. The values are 

Engineering Roadmap

Process Change

Administrative Hiring

Technical Debt

Infrastructure

Customer Service

Sales/Business Development

Adopt new Technology

4. In the source sheet if I add the following function, it works ok

5. =COUNTIF(Category:Category, FIND("Technical Debt", @cell) > 0)

6. I want to create a dashboard, so I have a blank sheet where I add sheet summaries.

7. The following function is not working when I refer to the "MySheet" source sheet "Category" column.

8. =COUNTIF({MySheet Category}:{MySheet Category}, FIND([Primary Column]@row, @cell)> 0)

9. Note on the Blank Sheet I am aggregating the counts for various columns. 

10. The Primary Column of this sheet I added the values above from step 3.

What is not right with the reference range? How might I fix my reference to make it work on the new sheet? Any help is much appreciated.

Thanks in advance,

RG

Genevieve P.

Hi @love_ss_2023

If you're looking into a Multi-Select column, you can use the HAS Function to see if a cell has a certain selection.

For example:

=COUNTIF(Category:Category, HAS(@cell, "Technical Debt"))

Then if you're looking to do the same formula but across sheets, you'll want to select the range by clicking on the "Reference Another Sheet" hyperlink that pops up as you write a formula.

These cross-sheet references only need to be listed once, so your new formula would look like this:

=COUNTIF({MySheet Category}, HAS(@cell, [Primary Column]@row))

Here's more information: Create cross sheet references to work with data in another sheet

And see: Create a cell or column reference in a formula

Cheers,

Genevieve

Genevieve P.

Hi @love_ss_2023

If you're looking into a Multi-Select column, you can use the HAS Function to see if a cell has a certain selection.

For example:

=COUNTIF(Category:Category, HAS(@cell, "Technical Debt"))

Then if you're looking to do the same formula but across sheets, you'll want to select the range by clicking on the "Reference Another Sheet" hyperlink that pops up as you write a formula.

These cross-sheet references only need to be listed once, so your new formula would look like this:

=COUNTIF({MySheet Category}, HAS(@cell, [Primary Column]@row))

Here's more information: Create cross sheet references to work with data in another sheet

And see: Create a cell or column reference in a formula

Cheers,

Genevieve

love_ss_2023

Thank you so much! It solved our issue.

Best Regards,

RG