Mulitple Ifs

[PCRIDE]

Here is my formula

IF('Table'!$D$4="Apples",SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100),IF('Table'!$D$4="Orange",SUMIF($A$118:$A$213,'Table'!$D$1,$E$118:$E$213)*SUMIF($C$53:$C$100,AP3,$E$53:$E$100),IF('Table'!$D$4="Bananna",SUMIF($A$118:$A$213,'Table'!$D$1,$F$118:$F$213)*SUMIF($C$53:$C$100,AP3,$F$53:$F$100),IF('Table'!$D$4="Grape",SUMIF($A$118:$A$213,'Table'!$D$1,$G$118:$G$213)*SUMIF($C$53:$C$100,AP3,$G$53:$G$100),IF('Table'!$D$4="(All)",SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100))))))


If its Apples, SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100)

It goes on through multiple checks.


If its non of them and its "(ALL)" then it will sum everything.

If, for some reason when the criteria is NOT ALL (this is from a pivot table filter label) it doesn't sum the section the pivot is filtered on. Apples, Organges.. etc...

Am I missing something?

Here is a cleaner view

IF('Table'!$D$4="Apples",SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100),
IF('Table'!$D$4="Orange",SUMIF($A$118:$A$213,'Table'!$D$1,$E$118:$E$213)*SUMIF($C$53:$C$100,AP3,$E$53:$E$100),
IF('Table'!$D$4="Bananna",SUMIF($A$118:$A$213,'Table'!$D$1,$F$118:$F$213)*SUMIF($C$53:$C$100,AP3,$F$53:$F$100),
IF('Table'!$D$4="Grape",SUMIF($A$118:$A$213,'Table'!$D$1,$G$118:$G$213)*SUMIF($C$53:$C$100,AP3,$G$53:$G$100),
IF('Table'!$D$4="(All)",SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100))))))


Thanks,

 

[Excelestial]

Firstly, your formula will look for the text (All)...is that how it appears as an entry?
Secondly, you are asking SUMIF to match (All) in column H. Is there an (All) in column H?
When the criteria is "All" or "(All)", don't use the conditioning SUMIF. Use SUM instead because you want everything summed in your targeted range.

 

[PCRIDE]

Yes (ALL) is the pivot table filter lable when all fruits are selected.

I am just saying if D4=(ALL) then Sumif(

Because I have a critera built in. The critera is
SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100

A is a Month, D1 is supposed to be D2, but its suming the numbers based on a month criteria then multiplies by % based on another criteria.

Basically as the user changes the pivot table filter, the charts update. I am using slices to change the pivot table (Excel 2010)

 

[PCRIDE]

I got it working, I had to put the month infront of the very last formula.


Now, can I encapsulate this entire forumla into 1 function because I have different companies and I need to repeat this same formula with a higher level IF Statement...


Example

If(A1="CompanyB",IF('Table'!$D$4="Apples",SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100),IF('Table'!$D$4="Orange",SUMIF($A$118:$A$213,'Table'!$D$1,$E$118:$E$213)*SUMIF($C$53:$C$100,AP3,$E$53:$E$100),IF('Table'!$D$4="Bananna",SUMIF($A$118:$A$213,'Table'!$D$1,$F$118:$F$213)*SUMIF($C$53:$C$100,AP3,$F$53:$F$100),IF('Table'!$D$4="Grape",SUMIF($A$118:$A$213,'Table'!$D$1,$G$118:$G$213)*SUMIF($C$53:$C$100,AP3,$G$53:$G$100),IF('Table'!$D$4="(All)",SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100)))))

then another one

if A1=Company C, IF('Table'!$D$4="Apples",SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100),IF('Table'!$D$4="Orange",SUMIF($A$118:$A$213,'Table'!$D$1,$E$118:$E$213)*SUMIF($C$53:$C$100,AP3,$E$53:$E$100),IF('Table'!$D$4="Bananna",SUMIF($A$118:$A$213,'Table'!$D$1,$F$118:$F$213)*SUMIF($C$53:$C$100,AP3,$F$53:$F$100),IF('Table'!$D$4="Grape",SUMIF($A$118:$A$213,'Table'!$D$1,$G$118:$G$213)*SUMIF($C$53:$C$100,AP3,$G$53:$G$100),IF('Table'!$D$4="(All)",SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100))))))


I have 7 companies. Can excel nest this many formula? I thought there was a limit of 7.

If I can tell the excel chart to chart a different range if Company A is selected that would work as well.

Please advise.

Thanks,

 

[pedie]

What does this formula do, just curious?
I have never seen such a long formula....by the time i validate to the end i'll be like mad....


thanks fo helping out though!!!

 

[shemayisroel]

PCRIDE, rather than putting your formula in, why not try placing some sample data, explain what your looking for (your desired result) and perhaps you may get some more helpful answers.

 

[Aladin Akyurek]

I got it working, I had to put the month infront of the very last formula.


Now, can I encapsulate this entire forumla into 1 function because I have different companies and I need to repeat this same formula with a higher level IF Statement...


Example

If(A1="CompanyB",IF('Table'!$D$4="Apples",SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100),IF('Table'!$D$4="Orange",SUMIF($A$118:$A$213,'Table'!$D$1,$E$118:$E$213)*SUMIF($C$53:$C$100,AP3,$E$53:$E$100),IF('Table'!$D$4="Bananna",SUMIF($A$118:$A$213,'Table'!$D$1,$F$118:$F$213)*SUMIF($C$53:$C$100,AP3,$F$53:$F$100),IF('Table'!$D$4="Grape",SUMIF($A$118:$A$213,'Table'!$D$1,$G$118:$G$213)*SUMIF($C$53:$C$100,AP3,$G$53:$G$100),IF('Table'!$D$4="(All)",SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100)))))

...

Create a range housing:

Apples
Orange
Bananna
Grape
(All)

and name this range List.

Now invoke instead the above the leghthy If-formula:

 =IF(A1="CompanyB",
     SUMIF($A$118:$A$213,Table!$D$1,INDEX($D$118:$H$213,0,MATCH(Table!$D$4,List,0)))*
       SUMIF($C$53:$C$100,AP3,INDEX($D$53:$H$100,0,MATCH(Table!$D$4,List,0))),
     "")

 

[PCRIDE]

Thanks, I will try the range.

The dashboard I am working on is way too complex to even try to post/explain.

So maybe I can say this.

I have a 42 rows that contain data , when the user wants to see the sales of "Apples" by Company, they simply select the combination. The user can slice the data any which way. This is done by a pivot table more or less with addtional formulas.

The sales chart updates but the sales graph series, needs to change based on the company selected. I was trying to contain all this in one cell range so I don't have to build out multiple sales charts or tables for each company. Since the chart sales series is locked into one column range, I can't tell the chart to move the series to a different range (just for that series, everything else stays the same.)

Unless anyone knows that.

 

[PCRIDE]

Not sure I understand this. I have not used Index and match before and really can't re-work all the formulas now.

Is there a workaround? or Can I use the list range in the same formula?

Create a range housing:

Apples
Orange
Bananna
Grape
(All)

and name this range List.

Now invoke instead the above the leghthy If-formula:

 =IF(A1="CompanyB",
     SUMIF($A$118:$A$213,Table!$D$1,INDEX($D$118:$H$213,0,MATCH(Table!$D$4,List,0)))*
       SUMIF($C$53:$C$100,AP3,INDEX($D$53:$H$100,0,MATCH(Table!$D$4,List,0))),
     "")

 

[Aladin Akyurek]

Not sure I understand this. I have not used Index and match before and really can't re-work all the formulas now.

I don't see why not. It gives you the means to do more and that in a coherent/debuggable fashion than a set up with zillions of IFs.

Is there a workaround? or Can I use the list range in the same formula?

A workaround for what? If you mean List (the range name I suggested), the answer is: Yes, whenever appropriate.