Using IF, AND, OR - Microsoft Community

hi all,

there simple solution question. i'm using excel 2007 , need create formula @ range l2:l15.

if following found -

1.1c or 1.1d or 1.1e or 1.1j

the formula should return 5000.

if results found other results such 4.3 or 1.3 found within l2:l15, return should 1000.

i have tried =if(or(l2:l15="1.1c",l2:l15="1.1d",l2:l15="1.1e",l2:l15="1.1j"),"5000","1000").  returns #value error if cells empty , doesn't consider other results found. think need use if, and, or how write evades me.

any appreciated. 

thanks in advance

a

hi andrew ,

there simple solution question. i'm using excel 2007 , need create formula @ range l2:l15.
if following found -
1.1c or 1.1d or 1.1e or 1.1j
formula should return 5000.
if results found other results such 4.3 or 1.3 found within l2:l15, return should 1000.
have tried =if(or(l2:l15="1.1c",l2:l15="1.1d",l2:l15="1.1e",l2:l15="1.1j"),"5000","1000").  this returns #value error if cells empty , doesn't consider other results found. think need use if, and, or how write evades me.

try:

=if(sumproduct(countif(l2:l15,{"1.1c";"1.1d";"1.1e";"1.1j"}))=counta(l2:l15),5000,1000)

===

regards,

norman

Office / Excel / Windows 10 / Office 2007