Simplify a Formula

Cristina Andersen

Greetings Everyone, Im trying to create a formula that reads across multiple columns. If any of the columns contain a red ryg ball, Id like a "Needs Attention" to be populated. So far, the formula below is helping but Id like to know if it can be simplified.

=IF(OR([Tail Guard]1 = "Red", [Tail Pulley]1 = "Red",[Stairs, Ladder]1 = "Red",[Hand Rail]1= "Red",[Cat Walk]1= "Red",[Bend Pulley Guard]1= "Red", [Bend Pulleys]1="Red",[Counterweight Pulley]1="Red",[Head Pulley Guard]1="Red",[Head Pulley]1="Red",[V Belt Guard]1="Red",[V Belts]1="Red",[Belt Wiper]1="Red",[Splice]1="Red",[Tracking]1="Red",[Idlers]1="Red"), "Needs Attention", "No Further Action Required")

Paul Newcome

Are all of the columns next to each other, or are there any columns in between that could contain "Red"?

You could try counting across all columns and say that if the count of reds is greater than zero (meaning at least one is red) then "Needs Attention".

=IF(COUNTIFS([Tail Guard]@row:Idlers@row, "Red") > 0, "Needs Attention", "No Further Action Required")

Paul Newcome

Are all of the columns next to each other, or are there any columns in between that could contain "Red"?

You could try counting across all columns and say that if the count of reds is greater than zero (meaning at least one is red) then "Needs Attention".

=IF(COUNTIFS([Tail Guard]@row:Idlers@row, "Red") > 0, "Needs Attention", "No Further Action Required")

Cristina Andersen

Paul, they are spread throughout the sheet.

Paul Newcome

Ok. If you don't have any columns in between that could also be "Red" that you would want excluded, then you should still be able to use the COUNTIFS.

Cristina Andersen

Paul, heres another one Im hoping you can help me simplifiy....multiple quantity columns with multiple items columns that may or may not have the same values. Right now I am stringing together these sum ifs...

=SUMIFS({Quantity}, {Item}, CONTAINS("D6-20E-48", @cell)) + SUMIFS({Quantity 2}, {Item 2}, CONTAINS("D6-20E-48", @cell))

Paul Newcome

Because you are summing two different ranges using different range/criteria sets, that is how you will have to go about doing it. Adding together two SUMIFS.

Cristina Andersen

Thank you Paul!!

Paul Newcome

Happy to help. 👍️

Cristina Andersen

Good morning Paul, can you help with this question? I cant seem to get a helpful answer...This formula needs to go into a Sheet Summary. Can you use @row in the summary?

I have a multi-select column and a created date column(System). Im trying to get the netdays since at least one selection in the multi select was chosen and a row was created for it. I know Im missing a CONTAINS in here...I think...help is appreciated.

=IF(Pri/Sec/Base/Water/Sand @row = "Primary", TODAY() - [Created] @row, "")

Paul Newcome

You cannot use "@row" in a Sheet Summary formula because the formula is not residing on an actual row. You can use "@cell" and cell references though.

Exactly what are you wanting to accomplish with the formula?

Cristina Andersen

I need to count how many days its been between certain events. So for instance, I wanted to know when was the last time the primary had something happen at it.

Paul Newcome

Are you able to provide a screenshot with some sample data?

Cristina Andersen

Yes sir. Below is the example. I want to know how many days its been since the "Primary" has been greased. Based off of the Created date.

Paul Newcome

Ok. Then you are going to want to use something along the lines of...

=TODAY() - DATEONLY(MAX(COLLECT(Created:Created, Location:Location, CONTAINS("Primary", @cell))))

Basically we collect all of the cells from the Created column where the Location column contains "Primary". Then we pull the most recent date using the MAX function. Then we extract the date from that using the DATEONLY function. Finally we subtract that date from today's date, and that will tell you how many days it has been since the last row was created that has "Primary" in the Location column.

Cristina Andersen

Thank you so much!!! I have used so much of your help in other formulas!! Here's another one for the books!

Here's one more if you can....I have three plants with many, many components. I'd like to make a formula that evaluates the component names( up to 30) and returns either plant 1, 2, or 3. I know I can create an IF formula for a single plant. But how can I build it to return 3 possibles?

Paul Newcome

Are you pulling this into a 4th sheet? Are you able to provide a screenshot of the destination with a few sample outputs manually entered?