# Need assistance on #Invalid Operation result

Options
✭✭✭

I'm trying to create a formula in a parent row to return the most common value of its children (High, Medium, or Low) and keep getting an #Invalid Operation error

If there are equal or more highs than mediums or lows, the parent status should be High.

If there are equal or more mediums than highs or lows, the parent status should be Medium.

If there are equal or more lows than mediums or highs, the parent status should be Low.

=IF(AND(COUNTIF(CHILDREN(), "High") >= COUNTIF(CHILDREN(), "Medium"), >=COUNTIF(CHILDREN(), "Low")), "High", IF(AND(COUNTIF(CHILDREN(), "Medium") >= COUNTIF(CHILDREN(), "High"), >=COUNTIF(CHILDREN(), "Low"))), "Medium", IF(AND(COUNTIF(CHILDREN(), "Low") >= COUNTIF(CHILDREN(), "Medium"), >=COUNTIF(CHILDREN(), "High")), "Low"))

I also tried to add for it to return a N/A status if none of that is true and it came back with an #Unparseable error

=IF(AND(COUNTIF(CHILDREN(), "High") >= COUNTIF(CHILDREN(), "Medium"), >=COUNTIF(CHILDREN(), "Low")), "High", IF(AND(COUNTIF(CHILDREN(), "Medium") >= COUNTIF(CHILDREN(), "High"), >=COUNTIF(CHILDREN(), "Low"))), "Medium", IF(AND(COUNTIF(CHILDREN(), "Low") >= COUNTIF(CHILDREN(), "Medium"), >=COUNTIF(CHILDREN(), "High")), "Low", “N/A”))

What am I doing wrong?

Tags:

• ✭✭✭✭✭✭
Options

Lets try this...

=IF(COUNTIFS(CHILDREN(), "High") >= COUNTIFS(CHILDREN(), OR(@cell = "Low", @cell = "Medium")), "High", IF(COUNTIFS(CHILDREN(), "Medium") >= COUNTIFS(CHILDREN(), OR(@cell = "High", @cell = "Low")), "Medium", "Low"))

• ✭✭✭
Options

Thanks @Paul Newcome! It's counting blank values and the formula is returning "High" to the parent row (even if all children are blank). Any advice on how I could return a "Not Applicable" if rows are blank instead? If counts are equal among all (High, Medium, Low), it is returning "Low." Is there a way to switch that to return the highest value if they are all equal?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!