Looking for a formula to look down a column to entries that are the same and looking at a status

MJ215

Hi,

I am various reports that will be generated under 1 intake challenge ID as these reports will go to different sets of approvers depending on location. Is there are formula that will go down this column and check to see that everything under a unique Challenge ID has been Approved by All?

I need a column for this to link back to my intake sheet so I can filer anything that has been Approved or Rejected out of my Data Connector that uses Alteryx to garb intake requests and do comparisons to create the reports that need the approval tracking.

Once a specific Challenge ID created in the Smartsheet with the Intake Form has been completed and everything is either "Report Approved By All Approvers" or "Rejected" I would want that NTIA Challenge ID Approval Status to Update so I can pull in that status by the Challenge ID in from the intake for my filter.

Genevieve P.

Hi @MJ215

If I'm understanding your process correctly, we can use a COUNTIF function to COUNT how many times the specific Challenge ID appears in your sheet, and compare that to another COUNTIFS which counts how many times that specific Challenge ID has the value "Report Approved By All Approvers" in the Approval Status column.

If the Counts are the same, then all instances of that Challenge ID are approved. If not, then there's still at least one that is pending.

Here's the structure:

=IF(

COUNTIF(Challenge ID) = COUNTIFS(Challenge ID and status),

"All Approved", "Pending")

So, try something like this:

=IF(COUNTIF([Challenge ID]:[Challenge ID], [Challenge ID]@row) = COUNTIFS([Challenge ID]:[Challenge ID], [Challenge ID]@row, [NTIA Report Approval Status]:[NTIA Report Approval Status], "Report Approved By All Approvers"), "All Approved", "Pending")

Let me know if this works for you, or if you'd like to see screen captures!

Cheers,

Genevieve

Genevieve P.

Hi @MJ215

If I'm understanding your process correctly, we can use a COUNTIF function to COUNT how many times the specific Challenge ID appears in your sheet, and compare that to another COUNTIFS which counts how many times that specific Challenge ID has the value "Report Approved By All Approvers" in the Approval Status column.

If the Counts are the same, then all instances of that Challenge ID are approved. If not, then there's still at least one that is pending.

Here's the structure:

=IF(

COUNTIF(Challenge ID) = COUNTIFS(Challenge ID and status),

"All Approved", "Pending")

So, try something like this:

=IF(COUNTIF([Challenge ID]:[Challenge ID], [Challenge ID]@row) = COUNTIFS([Challenge ID]:[Challenge ID], [Challenge ID]@row, [NTIA Report Approval Status]:[NTIA Report Approval Status], "Report Approved By All Approvers"), "All Approved", "Pending")

Let me know if this works for you, or if you'd like to see screen captures!

Cheers,

Genevieve