Need help with a formula to determine if all responses have been received for each group in the Smartsheet

Question

Hi!  Thanks in advance for taking a look!

I have a Smartsheet that has a line for each person being reviewed as well as their "client number".

I created a helper row to let me know when each person has completed their actions, but I'm having a hard time finding a way to indicate when everyone for that client number  has completed all actions.

I created a report and grouped by client ID and then status of review completed yes or no (if the user info is blank still).

However, unless I look at the 2,000+ customer rows separately, I can't easily check if all users are in the yes group or if I still have outstanding responses.

Is there a way to do this either in the master sheet or by doing something else in the report?

Thanks!

jmyzk_cloudsmart_jp · Answer

Regarding the'too many cells referenced by formulas error ', the following formula uses two references, one reference fewer than the previous formula.

=INDEX(COLLECT({All responses have been received for Client?}, {Client ID}, Client@row), 1)

https://app.smartsheet.com/b/publish?EQBCT=5b05793d57974ff2b2c43f5522cefb51

BtYour formula looks strange.

=COUNTIFS({Data Sheet Range 3}, "Yes"),AND({Data Sheet Range 5}:[Context ID]@row)

First, the COUNTIFS function's syntax is as follows.

COUNTIFS(range1, criterion1, [range2, criterion2, ...])

So, you do not need the AND, but format the formula like this;

=COUNTIFS({Data Sheet Range 3}, "Yes"),{Data Sheet Range 5}, [Context ID]@row)

asimcock · Answer

It looks like I may need to phone a friend again, apologies, but I've run into the too many cells referenced by formulas error now.

Prior to this workflow I also have  a separate sheet that is form responses, that are pulling into the sheet. (this is what helps define the YES depending on what answers are received via the form.)

So I created a Client summary sheet with Client IDs and I was able to replicate all formulas except the one to count the "Yes" for only the Client ID on that row.

I tried using a countifs referencing first the Review Completed? "Yes" AND if Client ID on summary sheet matches.  It can successfully count the "Yes" for the full sheet but I get an Unparseable error when trying to limit count to only the client ID on that row

=COUNTIFS({Data Sheet Range 3}, "Yes"),AND({Data Sheet Range 5}:[Context ID]@row). I've confirmed that both the Client ID and Review Completed" Columns exist in both references to the other sheet.

Any recommendations would be greatly appreciated! Thank you :)

asimcock · Answer

Thank you so much@jmyzk_cloudsmart_jp

That was exactly what I needed, from this I was able to add another column to make sure the count of prescribers matched the responses. :)