Formula to summarize data from symbols column for same row ID

System

This discussion was created from comments split from: Countifs formula with and/or criteria across multiple columns.

Georgie

Hi @jgrzywacz,

That’s right, you’ll need a nested IF formula using COUNTIFS in your “Final Disposition” column. Try this:

• =IF(COUNTIF([Manuscript ID]:[Manuscript ID], [Manuscript ID]@row) > 1, IF(COUNTIFS([Manuscript ID]:[Manuscript ID], [Manuscript ID]@row, [Advance for Review]:[Advance for Review], "Yes") = 3, "Yes", "Hold"))

Then, for your automation, I’d recommend the following:

1. Add an autonumber column named “Row ID”, and save your sheet to populate it.
2. Once you’ve got a unique row ID for each row, you can then create a helper column with a formula that returns the Manuscript ID for only the first row where each Manuscript ID appears. Try: =IF([Row ID]@row = MIN(COLLECT([Row ID]:[Row ID], [Manuscript ID]:[Manuscript ID], [Manuscript ID]@row)), [Manuscript ID]@row). Your sheet should then look similar to this (but with your additional columns!):
   
3. You can hide the helper column as it doesn’t need to be seen, but you’ll use it to trigger your automation.
4. Set up your automation to trigger when rows are changed - when Final Disposition changes to “Yes”, and add a condition block for when your Helper column is not blank, and set the action block to Alert Someone and set your options as desired - your automation should look similar to below:

Hope that helps!

Georgie

jgrzywacz

I am a new user with very little background in formulas, so I am hoping for some help! For context, as part of an editorial process, I distribute a manuscript ("Manuscript ID") to three people ("Reviewer") and ask the question, "Should this paper be distributed for further review?" Each "Reviewer" answers the question with a categorical symbol ("Advance for Review") such that a green check indicates "yes," a red X indicates "no," and a yellow exclamation point (not shown) indicates "I want to discuss". I receive an automation trigger when I have three responses for each manuscript ("Ballot Complete" — =IF(COUNTIFS([Manuscript ID]:[Manuscript ID], @cell = [Manuscript ID]@row) = 3, 1) ) telling me it is time to make a decision.

Now my question. I want to use my "Final Disposition" column to summarize data in the "Advance for Review" column, but only in reference to data in the "Manuscript ID" column where the manuscript ID matches. If all three recommendations ("Advance for Review") for the same manuscript are green check boxes, I want "Final Disposition" to have a green check box. If all three recommendations for the same manuscript are a red X, I want the "Final Disposition" to have a red X. If there is any combination of green, yellow, or red across the three decisions (Advance for Review"), I want the "Final Disposition" to have a yellow exclamation point. I presume I need some type of nested COUNTIFS formula, but I can't seem to figure that out.

Finally, for extra credit😅, I want to use the "Final Disposition" column to fuel a distinct automation. However, I want only one automation per manuscript ("Manuscript ID"), not one automation for each row. How do I do that?

Happy Friday and wonderful weekend to you!

Georgie

Hi @jgrzywacz,

That’s right, you’ll need a nested IF formula using COUNTIFS in your “Final Disposition” column. Try this:

• =IF(COUNTIF([Manuscript ID]:[Manuscript ID], [Manuscript ID]@row) > 1, IF(COUNTIFS([Manuscript ID]:[Manuscript ID], [Manuscript ID]@row, [Advance for Review]:[Advance for Review], "Yes") = 3, "Yes", "Hold"))

Then, for your automation, I’d recommend the following:

1. Add an autonumber column named “Row ID”, and save your sheet to populate it.
2. Once you’ve got a unique row ID for each row, you can then create a helper column with a formula that returns the Manuscript ID for only the first row where each Manuscript ID appears. Try: =IF([Row ID]@row = MIN(COLLECT([Row ID]:[Row ID], [Manuscript ID]:[Manuscript ID], [Manuscript ID]@row)), [Manuscript ID]@row). Your sheet should then look similar to this (but with your additional columns!):
   
3. You can hide the helper column as it doesn’t need to be seen, but you’ll use it to trigger your automation.
4. Set up your automation to trigger when rows are changed - when Final Disposition changes to “Yes”, and add a condition block for when your Helper column is not blank, and set the action block to Alert Someone and set your options as desired - your automation should look similar to below:

Hope that helps!

Georgie

jgrzywacz

Thank you, Georgie — it worked perfectly! Thank you for modeling simplistic coding for this new user!🤩