How to correctly refer to another sheet for COUNTIF?



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


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,



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!