Reference Sheet CountIfs Formula

lukasrobbo97
lukasrobbo97 ✭✭
in Formulas and Functions

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

Tags:
· Share on FacebookShare on Twitter

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    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.

    · Share on FacebookShare on Twitter
  • lukasrobbo97
    lukasrobbo97 ✭✭
    https://community.smartsheet.com/discussion/comment/322353#Comment_322353

    @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

    · Share on FacebookShare on Twitter
  • 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.

    image.png











    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

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

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • David Tutwiler
    David Tutwiler Overachievers Alumni

    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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!