# Mulitple Ifs

#### PCRIDE

##### Well-known Member
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
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
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
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.

Thanks,

#### pedie

##### Well-known Member

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

##### Well-known Member
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.

##### MrExcel MVP

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
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
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))),
"")``````

##### MrExcel MVP
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. 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

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.

### Which adblocker are you using?    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

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