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

cnikkih

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?

KPH

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.

KPH

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.

cnikkih

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!

KPH

No problem. Glad we got you unblocked. I'm sure you'll find the cross sheet formulas really useful.