COUNTIFS - cross-sheet reference using multiple criteria

Jon Crockett

I can't seem to get the second criteria to work. My formulas for each work fine, but I need both to reduce count to only those with both criteria.

Range 1: {Project Plan [ALL]} - highlighted all columns in sheet

Range 2: {Project Plan [Status]} - highlighted one column labelled "Status" with drop down boxes with colors (ie "Red", Yellow", "Green")

=COUNTIFS({Project Plan [ALL]}, ="P0. Pre-Close"),{Project Plan [Status]},="red")

Help would be greatly appreciated.

* Too bad Smartsheets doesn't have an Fx drop down tool to help with these formulas.

Kelly Moore

Hey @Jon Crockett

You have closed off the COUNTIFS function too early. Remove the parenthesis in the middle of the formula.

=COUNTIFS({Project Plan [ALL]}, ="P0. Pre-Close",{Project Plan [Status]},="Red")

Smartsheet does have a function helper - a dialog box pops up anytime you are using a function to show you syntax and where smartsheet things you are (the highlighted text in that window) for that function.

Will the formula work for you?

Jon Crockett

Nope. I think I understand the { } to reference another sheet.

Maybe it's my ranges. I have the [ALL] highlighting all columns. I have two others in the same sheet referencing only single columns with the data.

Seems so intuitive and yet I keep getting errors.

I run the formulas with single criteria and they both work. I just want two criteria to limit the counting.

Jon Crockett

But I did notice my error on the extra ). Thank you. Just frustrating that the formula won't work.

Jon Crockett

The dialog pop-up box doesn't help me link to existing Reference Names in Reference Manager. Instead I have to type the exact name each time.

Jon Crockett

I just tried this but it counts both rather than limit:

=COUNTIFS({Project Plan [ALL]}, OR(@cell = "P3. Transform", @cell = "Red"))

I want the count only if both criteria are met

Kelly Moore

@Jon Crockett

The OR function will be true if either of the criteria are true. The AND function will force each criteria to be met. Within your sheet do you have multiple status columns that could be Red? Since you set a range that references your entire sheet (this is atypical), any 'Red' fields will be counted. You could consider setting ranges to only specific column(s). This allows more granular filtering of data.

=COUNTIFS({Project Plan [ALL]}, AND(@cell = "P3. Transform", @cell = "Red"))

In terms of referencing existing Reference Names. One can type the entire reference in again. An alternate is to use the Insert Reference link. If you select a column that has already been referenced, the Reference name will be inserted. Having smartsheet insert the link also is a good double check that the correct range is being referenced.

Kelly

Jon Crockett

Resolved.

i had two browsers open and the formulas wouldn’t calculate. I closed one and then refreshed and saved my sheet and the formulas all of a sudden worked.

so weird. I knew I was writing it correctly, lol.

Thank you for the help.