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

  • KPH
    KPH Community Champion
    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

  • KPH
    KPH Community Champion
    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.

  • cnikkih
    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!

    Nikki Hailey
    Lead Systems Analyst - Jesta Sourcing & Demand
    Admin - Jira/Confluence, Smartsheet, Lucid
    Genesco Inc.
    535 Marriott Drive
    Nashville, Tennessee37214
    nhailey@genesco.com

  • KPH
    KPH Community Champion

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!