Cannot make a formula with a cell reference to another sheet work as expected.

I'm trying to count the number of Blocker (priority) tickets for a specific Business Unit. This is the formula that SHOULD work:
=COUNTIFS({BU}, [Business Unit or Dept]:[Business Unit or Dept], "JM", {Priority}, [Priority]:[Priority], "Blocker", {Ticket}, [Jira Ticket #]:[Jira Ticket #], <>"").
I have validated that the formula works in in the origin sheet without the sheet references, so I know the column names and logic is correct. I have validated that the range names ({BU}, {Priority}, {Ticket}). What is happening?
Nikki Hailey
Lead Systems Analyst - Jesta Sourcing & Demand
Admin - Jira/Confluence, Smartsheet, Lucid
Genesco Inc.
535 Marriott Drive
Nashville, Tennessee37214
nhailey@genesco.com
Best Answer
-
Hi @cnikkih
There is a problem with how the cross sheet references are being used. You do not insert the cross sheet reference before the column reference, you replace the column reference with the cross sheet reference to the column. Assuming:
- "BU" is the name you gave to the column "Business Unit or Dept" in another sheet
- "Priority" is the name you gave to the column "Priority" in another sheet
- "Ticket" is the name you gave to the "Jira Ticket #" in another sheet
Then your formula would look like this:
=COUNTIFS({BU}, "JM", {Priority}, "Blocker", {Ticket}, <>"").
The syntax of the COUNTIFS is the same whether it is within a sheet or cross sheet. It is always range, comma, criteria, comma, range, comma, criteria.
Adding unexpected things to the function will prevent it from working.
Answers
-
Hi @cnikkih
There is a problem with how the cross sheet references are being used. You do not insert the cross sheet reference before the column reference, you replace the column reference with the cross sheet reference to the column. Assuming:
- "BU" is the name you gave to the column "Business Unit or Dept" in another sheet
- "Priority" is the name you gave to the column "Priority" in another sheet
- "Ticket" is the name you gave to the "Jira Ticket #" in another sheet
Then your formula would look like this:
=COUNTIFS({BU}, "JM", {Priority}, "Blocker", {Ticket}, <>"").
The syntax of the COUNTIFS is the same whether it is within a sheet or cross sheet. It is always range, comma, criteria, comma, range, comma, criteria.
Adding unexpected things to the function will prevent it from working.
-
Ahhh, ok. I swear I looked it up before coming here, but I guess I didn't truly understand. You are a magical translator of all things Smartsheet. Thank you so much for your help!
Nikki Hailey
Lead Systems Analyst - Jesta Sourcing & Demand
Admin - Jira/Confluence, Smartsheet, Lucid
Genesco Inc.
535 Marriott Drive
Nashville, Tennessee37214
nhailey@genesco.com -
No problem. Glad we got you unblocked. I'm sure you'll find the cross sheet formulas really useful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 150 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!