Mulitple Ifs

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
892
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,
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Excelestial

Well-known Member
Joined
Jan 2, 2009
Messages
828
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

Well-known Member
Joined
Jan 20, 2008
Messages
892
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

Well-known Member
Joined
Jan 20, 2008
Messages
892
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

Well-known Member
Joined
Apr 28, 2010
Messages
3,875

ADVERTISEMENT

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!!!:eek:
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210

ADVERTISEMENT

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:
Rich (BB code):
 =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

Well-known Member
Joined
Jan 20, 2008
Messages
892
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

Well-known Member
Joined
Jan 20, 2008
Messages
892
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:
Rich (BB code):
 =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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,279
Messages
5,836,363
Members
430,424
Latest member
ALHK022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top