What Formula would I used to get a return value if a range of cells in a column all have the same sp

Marciano L

I essentially want the image status to be "To Be Shot" if all of the cells in the sample status are marked as "RECEIVED". Then if all of the cells in a range DO NOT say "RECEIVED", I want the image status cell to say "MM". I've been using the following formula BUT I hate having to type out each cell number in the sample status I want it to reference. It won't let me choose a range of cells. I will use this formula multiple times in one sheet so it has to be a specific range and not the entire column. Can anyone help?

Formula being used currently:

=IF(AND([samples satus]36 = "Received", [samples satus]37 = "Received", [samples satus]38 = "Received", [samples satus]39 = "Received"), "To Be Shot", "MM"|

Ross Loomis

it looks like you are using indent. I would use this to help. try using the formula below

=IF(COUNTIF(CHILDREN([Sample Status]@row), ="RECEIVED") = COUNT(CHILDREN([Sample Status]@row)), "To Be Shot", "MM")

Ross Loomis

This will check if the Parent Row also says RECEIVED if not it will say MM

=IF(AND((COUNTIF(CHILDREN([Sample Status]@row), ="RECEIVED") = COUNT(CHILDREN([Sample Status]@row))), [Sample Status]@row = "RECEIVED"), "To Be Shot", "MM")

Ross Loomis

it looks like you are using indent. I would use this to help. try using the formula below

=IF(COUNTIF(CHILDREN([Sample Status]@row), ="RECEIVED") = COUNT(CHILDREN([Sample Status]@row)), "To Be Shot", "MM")

Marciano L

@Ross Loomis Wow that worked great!! Is there a formula that would include the sample status for the parent row in addition to the children rows? Thank you!

Ross Loomis

This will check if the Parent Row also says RECEIVED if not it will say MM

=IF(AND((COUNTIF(CHILDREN([Sample Status]@row), ="RECEIVED") = COUNT(CHILDREN([Sample Status]@row))), [Sample Status]@row = "RECEIVED"), "To Be Shot", "MM")

Marciano L

@Ross Loomis That was perfect! Thank you so much!!