Reference Sheet CountIfs Formula

Hi I need help in doing a count ifs in another sheet.

My countifs formula currently works in the summary section of the sheet but when doing a sheet reference it doesn't.


The working countifs formula is: =COUNTIFS([Risk or Issue?]:[Risk or Issue?], "Risk", Status:Status, "Open", Project:Project, "Data Analytics & Reporting")


The new sheet needs to reference {Risk & Issue Log Range 2} sheet. Can anyone help?


Thanks,


Luke

Answers

  • David Tutwiler
    David Tutwiler Overachievers

    When referencing another sheet, you will need to create 3 ranges, one for each of the columns you are evaluating. So for [Risk or Issue?] you would create something like {Risk or Issue?} and reference it in place of your current column reference. Do that for each of the three ranges and you should be able to use the same formula and replace the Ranges and it will all work.

    If you still need help, just @mention my name on the comment so I see it.

  • @David Tutwiler would you be able to write the formula out for me. I want to check if the risk is open and then area it is in

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    edited 03/11/22

    @lukasrobbo97

    You need to create cross-sheet references when building the formula in your new sheet. When you get to the open parentheses here =COUNTIFS( Smartsheet's popup help box has a link to Reference Another Sheet. Click on that, find the sheet you want to reference, select the column, and name the reference something that's meaningful (not just SheetName Range 1). Do this for each range in your formula. Keep in mind, range sizes must match and they must be from the same sheet to work inside the same function.











    The Learn More link at the bottom takes you to the help page for the function, although the popup box also shows an example and the syntax.

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

  • David Tutwiler
    David Tutwiler Overachievers

    Sure. If we assume that the range for [Risk or Issue?] is {Risk or Issue?}, for Status is {Status} and for Project is {Project}, then you would write.

     =COUNTIFS({Risk or Issue?}, "Risk", {Status}, "Open", {Project}, "Data Analytics & Reporting")

    The key to all of this is setting up your Sheet References as you're writing the function on another sheet. You will simple select the entire column (ie. Risk or Issue? column) and then change the name to match.