Multiple if/and critera

[studio95]

Good afternoon ladies and gentlemen.

I am looking for a formula which will return a different cell value based on two IF\AND criteria.

i.e

I have three columns in the worksheet and one of the cells in one of the three columns titled "live", "lost" "won" (AB, AC, & AD). There will be a figure in one of the cells depending on whether a quotation is "live", has been "lost" or has been "won". There will never be a figure in more that one of these columns. I also have a column which just contains the text of either "live", "lost" "won" (I)

Further in the worksheet I have a column which records and invoice number (AX) for anything in the "won" column, anything which has been lost just states N/A in this column, however there have been instances where an entry has been listed as "won" and the customer has come off programme before we have had an opportunity to invoice. These are flagged as "OP" in said column.

I would like a formula in BD which says if AX="OP" & I = lost then return the value in AC, but if AX <> "OP" or <> "N/A" (i.e contains an invoice number or is blank) & I = "won" then return the value in AB

Hope this makes sense ?

 

[snjpverma]

Try this,

=IF(AND(AX2="OP",I2="Lost"),AC2,IF(OR(AX2<>{"OP","N/A"}),AB2,"No condition met"))

 

[studio95]

Try this,

=IF(AND(AX2="OP",I2="Lost"),AC2,IF(OR(AX2<>{"OP","N/A"}),AB2,"No condition met"))

Fantastic works perfectly.

Thank you so much