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 its a good idea for Amazon to divide its new second headquarters between two locations?

Discuss in The Lounge

Poll History