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

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!🤩