Reference Sheet CountIfs Formula

lukasrobbo97

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

David Tutwiler

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.

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

Jeff Reisman

@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.

David Tutwiler

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.