This is the formula I am using, but i have to complete this for a number of list and seems long winded, is there a shorter way

=IF(E172>1,"YES",IF(E173>1,"YES",IF(E174>1,"YES",IF(E175>1,"YES",IF(E176>1,"YES",IF(E177>1,"YES",IF(E178>1,"YES",IF(E179>1,"YES",IF(E180>1,"YES",IF(E181>1,"YES",IF(E182>1,"YES",IF(E183>1,"YES",IF(E184>1,"YES",IF(E185>1,"YES",IF(E186>1,"YES",IF(E187>1,"YES",IF(E188>1,"YES",IF(E189>1,"YES",IF(E190>1,"YES",IF(E191>1,"YES",IF(E192>1,"YES",IF(E193>1,"YES",IF(E194>1,"YES",IF(E195>1,"YES",IF(E196>1,"YES",IF(E197>1,"YES","NO"))))))))))))))))))))))))))

You can count how many times a value greater than 1 appears in a range. If the count is >0, return "YES". =IF(COUNTIF(E172:E197,">1")>0,"YES","NO")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History