How to correctly refer to another sheet for COUNTIF?

Options

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

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!