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,
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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,201

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,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

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