Multiple IF and AND Statement - Need Help!

[londoneye001]

Hi - I was wondering if one of the Excel guru can correct the below formula for me? Formula it self is simple but even after the false part of the if statement I need the formula to continue to the next if statement so that next criteria can be matched. If there is a better way to do the allocation, I'm open to that as well.

Many Thanks,
Sid


=IF(AND(J4="Q1", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")=0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")*AA4), IF(AND(J4="Q2", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")=0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")*AA4), IF(AND(J4="Q3", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")=0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")*AA4), IF(AND(J4="Q4", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")=0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")*AA4)

 

[wideboydixon]

I don't understand your formula. When do you want to divide by 13 and when do you want to multiple by AA4? What's the significance of the first SUMIFS returning zero?

WBD

 

[londoneye001]

I don't understand your formula. When do you want to divide by 13 and when do you want to multiple by AA4? What's the significance of the first SUMIFS returning zero?

WBD

Hi - Thanks for your response. Let me try to explain the formula:

Criteria are:

• J4 = Q1, Q2, Q3, Q4 (These are Quarters)
• SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")=0) < this statement is checking if previous year sales is zero for the quarter or not
• If above statement returns zero (i.e. sales of previous quarters for this specific chain is zero) then the Quarterly forecast value has to be divided by 13
• if above statement is not zero then allocate Quarterly forecast value based on last year % of Sales (I have added a calculated field which calculates % of sales based on this formula Weekly Sales / Quarterly Sales <<< this formula is on a separate tab

I need my formula to work by Quarters. I hope this make sense?

Thanks,

 

[thisoldman]

This might be a little easier to read:

=IF(AND(J4="Q1", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")=0),
                 SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")/13,
                 SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")*AA4)

, IF(AND(J4="Q2", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")=0),
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")/13,
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")*AA4)

, IF(AND(J4="Q3", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")=0),
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")/13,
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")*AA4)

, IF(AND(J4="Q4", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")=0),
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")/13,
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")*AA4)

It's still confusing – there are four complete IF formulas concatenated with commas.

When the SUMIFS is zero, we divide that zero by thirteen.
When the SUMIFS is not zero, we multiply the SUMIFS by AA4.

 

[londoneye001]

This might be a little easier to read:

=IF(AND(J4="Q1", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")=0),
                 SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")/13,
                 SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")*AA4)

, IF(AND(J4="Q2", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")=0),
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")/13,
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")*AA4)

, IF(AND(J4="Q3", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")=0),
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")/13,
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")*AA4)

, IF(AND(J4="Q4", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")=0),
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")/13,
                  SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")*AA4)

It's still confusing – there are four complete IF formulas concatenated with commas.

When the SUMIFS is zero, we divide that zero by thirteen.
When the SUMIFS is not zero, we multiply the SUMIFS by AA4.

Sorry about the confusion, I realized first part of the formula is incorrect. It should be like this:

=IF(AND(J4="Q1", SUMIFS(CL.Sales,CL.Division,'Summary Data'!$H4,CL.FiscalQtr,"Q1",CL.ChainCD,'Summary Data'!$A4)0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")*AA4)

but i need this formula to work for Q2, Q3 and Q4

 

[wideboydixon]

Hi - Thanks for your response. Let me try to explain the formula:

Criteria are:

• J4 = Q1, Q2, Q3, Q4 (These are Quarters)
• SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")=0) < this statement is checking if previous year sales is zero for the quarter or not
• If above statement returns zero (i.e. sales of previous quarters for this specific chain is zero) then the Quarterly forecast value has to be divided by 13
• if above statement is not zero then allocate Quarterly forecast value based on last year % of Sales (I have added a calculated field which calculates % of sales based on this formula Weekly Sales / Quarterly Sales <<< this formula is on a separate tab

I need my formula to work by Quarters. I hope this make sense?

Thanks,

Nope. The first three SUMIFS are identical as far as I can see and not checking previous years? Also, as @thisoldman suggests, there are four complete IF() statements separated by commas. Strange.

WBD

 

[Simon4s]

Hi - I was wondering if one of the Excel guru can correct the below formula for me? Formula it self is simple but even after the false part of the if statement I need the formula to continue to the next if statement so that next criteria can be matched. If there is a better way to do the allocation, I'm open to that as well.

Many Thanks,
Sid


=IF(AND(J4="Q1", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")=0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")*AA4), IF(AND(J4="Q2", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")=0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"2")*AA4), IF(AND(J4="Q3", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")=0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"3")*AA4), IF(AND(J4="Q4", SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")=0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"4")*AA4)

This... : CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period
appears 12 times in your formula. Can you not do it in another cell and define a name then put it in your formula instead? Lets do that.

1. In J5, if(j4="Q1",1),if(j4="Q2",2),if(j4="Q3",3),if(j4="Q4",4),"")
2. in J6,=SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,j5)
3. define J6, call it "total" (or w/e you want)
4. your 10 line formula is now: =if(total=0,total/13,total*AA4)

 

[londoneye001]

Nope. The first three SUMIFS are identical as far as I can see and not checking previous years? Also, as @thisoldman suggests, there are four complete IF() statements separated by commas. Strange.

WBD

Hi - No the first 3 statements are not identical at all. First Statement says CL.Sales (Last Year Sales) whereas other two statements say CF.Sales (Sales Forecast)

This is the correct formula:

=IF(AND(J4="Q1", SUMIFS(CL.Sales,CL.Division,'Summary Data'!$H4,CL.FiscalQtr,"Q1",CL.ChainCD,'Summary Data'!$A4)0), SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")/13, SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,"1")*AA4)

and like i said i need this formula to work for Q2, Q3 and Q4 also. Thanks

 

[thisoldman]

I think this might work:

=IF(SUMIFS(CL.Sales,CL.Division,'Summary Data'!$H4,CL.FiscalQtr,J4,CL.ChainCD,'Summary Data'!$A4)=0,
 SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,RIGHT(J4))/13,
 SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,RIGHT(J4))*AA4)

I don't know if CF.Period is a number or if it is text.

 

[wideboydixon]

=SUMIFS(CF.Sales,CF.Division,$H4,CF.ChainCD,$A4,CF.Period,RIGHT(J4,1))*IF(SUMIFS(CL.Sales,CL.Division,'Summary Data'!$H4,CL.FiscalQtr,J4,CL.ChainCD,'Summary Data'!$A4)=0,1/13,AA4)

WBD