EXCEL Formula help - Possible Nested IF's

[SexySarah]

Hiya,

I am trying to work something out but I am getting stressed.

I've been at it for hours now.

Maybe some angel can help.

For Column Sale date actual, I am trying to see if Sale_Date contains a Value and is not Blank, If it is blank I want it to return blank, if it is not blank, then I want to see if Cancelled Status equals either Sale or Install in progress, and then return the Sale Date, otherwise return the Sale Date if that condition is not met.

My attempt below does not work as the first row for example should return the Sale_Date:


=IF($A2="",""(IF(OR($B2="Sale",$B2="Installation in Progress"),$A2,"")),$A2)

Then I am trying to find the weeknumber in column D.

[Limor_OP]

hi,
try this:

=IF(C2<>"",SUM(IF(B2={"Installation in progress","sale"},C2)),"")

[SexySarah]

Hi thank you for this,

This still does not work as it is not returning the Sales Date when cancelled status is showing blank.

[Limor_OP]

So in both cases you want the sale date , no matter what the cancel status is
"I want to see if Cancelled Status equals either Sale or Install in progress, and then return the Sale Date, otherwise return the Sale Date"

=IF(C2<>"",C2,"")

[SexySarah]

I guess what I am trying to do is merge 2 formulas:

IF 'Sale Date' is Blank return blank, otherwise return Sale date and if Cancel status equals either Sale or Install in Progress, then Return Sale Date, ELSE return Blank .

i.e. =if(A2="","",A2) and =IF(OR($B2="Sale",$B2="Installation in Progress"),$A2,"")

I hope that makes sense.

[Glenn Kennedy]

So you want the weeknumber of the date in column A...


if there's a date in column A... or certain text appears in column B. Assuming that text can ONLY occur in column B IF there is a date in column A (and that WAS the case in your sample), you are going in circles for no good reason:

=IF(A2<>"",WEEKNUM(A2),"")

[SexySarah]

HI Glenn,

that helps with the Weeknumber, but I am still trying to workout the Sale Date issue as mentioned before.

S x

[Limor_OP]

What do you want the result to be if B = "Quotation"? you also want A2 ?
If so then it doesn't matter what value there is in column B as long as A is not empty you want A as the result.....

[Glenn Kennedy]

Amend your samole to show your expected results... a non-working formula is of limited value.

[Glenn Kennedy]

There si something fundamentally flawed with your logic.

You said:

For Column?*Sale date actual, I am trying to see if?*Sale_Date?*contains a Value and is not Blank,

If it (sale date) is blank I want it to return blank, (so: if any is date present = return date)

if it (sale date)is not blank,

then I want to see if Cancelled Status equalseither?*Sale?*or?*Install in progress, and then return the?*Sale Date, otherwise return the?*Sale Date?*if that condition is not met. This is saying... If Cancelled Status equals "Sale" or "IiP"... return the sale date... otherwise return the sale date.

All conditions of Cancelled Status mean return the sale date. It is NOT a criterion if there is only one possible outcome!!

So at a desparate last guess...

=IF(OR(A2<>"",B2<>""),A2,"")