# 2 IF statements needed? , lots of conditions help

#### darkbluecat

##### New Member
Okay so the first IF statement works:

IF(\$E\$1<>"none",SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1,Raw!\$A:\$A,'Emission factors'!\$G\$1),SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$A:\$A,'Emission factors'!\$G\$1)))

Where "none" is when no date is selected from a drop down. Raw refers to the raw data sheet, emissions factors is the sheet I'm working on. Now the last condition for each sumifs refers to the second drop down I've made next to the date drop down, referring to the 4 categories the data is in which is in a column in the raw data tab: this works with each of the 4 categories.

- now I have a fifth category in the drop down which is "All", similar to the "none" date selection where it returns all, for example if I chose none for the date selection and all for the category it would give me each of the things in the raw data I'm summing. - my question is how would I add this into the calculation above?

can't provide any more info as it's for work but the above calculation does work for the categories just need the values to return all in case "All" categories are selected where no specific category is selected. Colour coded text for what the calc is referring to. I just need an option in case "All" is selected rather than a specific category.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### darkbluecat

##### New Member
Okay so the first IF statement works:

IF(\$E\$1<>"none",SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1,Raw!\$A:\$A,'Emission factors'!\$G\$1),SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$A:\$A,'Emission factors'!\$G\$1)))

Where "none" is when no date is selected from a drop down. Raw refers to the raw data sheet, emissions factors is the sheet I'm working on. Now the last condition for each sumifs refers to the second drop down I've made next to the date drop down, referring to the 4 categories the data is in which is in a column in the raw data tab: this works with each of the 4 categories.

- now I have a fifth category in the drop down which is "All", similar to the "none" date selection where it returns all, for example if I chose none for the date selection and all for the category it would give me each of the things in the raw data I'm summing. - my question is how would I add this into the calculation above?

can't provide any more info as it's for work but the above calculation does work for the categories just need the values to return all in case "All" categories are selected where no specific category is selected. Colour coded text for what the calc is referring to. I just need an option in case "All" is selected rather than a specific category.
Sorry ignore the green Emission factors E\$4 in the second sumif

#### etaf

##### Well-known Member
at the moment - the only TEST is the \$E\$1<>"none"
Which will do the TRUE part
SO if the dropdown has anything other than NONE it will do this
Excel Formula:
``SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1,Raw!\$A:\$A,'Emission factors'!\$G\$1)``
otherwise it will be FALSE and so for a value of "NONE' in the dropdown it will do
Excel Formula:
``SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$A:\$A,'Emission factors'!\$G\$1)))``

So if the Dropdown now has an entry ALL
what do you want the formula to DO when ALL is selected from the dropdown ?
I'm not clear

#### darkbluecat

##### New Member
So when "all" is selected, it doesnt refer to Raw!\$A:\$A,'Emission factors'!\$G\$1 that's in both sumifs, similar to how Raw!\$W:\$W,'Emission factors'!\$E\$1 is only in the first sumifs (so that it looks at the drop down) whereas when "none" is selected it doesn't look at the date in raw data

#### etaf

##### Well-known Member

i'm sorry DONT understand exactly

What formula do you want for each of the 3 possible combinations

ALL
?

NONE
?

Anything else
?

Would you give the formula's you need to be executed for each of the above conditions please

#### darkbluecat

##### New Member
i'm sorry DONT understand exactly

What formula do you want for each of the 3 possible combinations

ALL
?

NONE
?

Anything else
?

Would you give the formula's you need to be executed for each of the above conditions please
"All" is selected in categories dropdown> SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1)
"none" is selected in dates drop down> SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$A:\$A,'Emission factors'!\$G\$1)

if category is selected from category drop down (not "all") > SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$A:\$A,'Emission factors'!\$G\$1)
if date is selected from date drop down (not "none") > SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1,Raw!\$A:\$A,'Emission factors'!\$G\$1)

^ this is what I want to happen depending on one drop down selected

\$E\$1 is my first dropdown for dates, \$G\$1 is my second drop down for categories

...then i think it'll get complicated if a date is selected but category isn't, or if category is but date isn't

#### etaf

##### Well-known Member

still confused

you have a dropdown E1 which has NONE or ALL in
whats in G1
i'm sorry i'm not following, this is where using XL2BB would help as you can show your spreadsheet
or load a sample onto dropbox or onedrive - with examples of what results you want
the more detail the quicker i think

your referring to names like categories dropdown , 1st dropdown, BUT i cannot see the spreadsheet

So what i can make out
you have E1 and G1, BUT they seem to be in the formulas

#### darkbluecat

##### New Member
the dropdown E1 has a list of dates including a none option (where the value returns the total of all the dates because no specific date is selected- this is done by just leaving out that condition in the sumifs if E1 <> none ).
the dropdown G1 has a list of the 4 categories and 1 more option which is all (where the value returns the total of all the 4 categories because no specific category is selected)

is there a way to incorporate the category dropdown into the formula in the same way the date condition (with dropdown) is in the formula?

#### etaf

##### Well-known Member
I'm thinking of a nested IF

IF ( AND ( E1 = "NONE" , G1 = "ALL" ) ,
=NONE
SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4)))
Then do a formula with NO Categories
NOW ALL
=IF( G1="ALL" ) - BUT E has dates , SO E1 does not = "NONE AND G1 does have ALL categories
SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1)

NEXT E1 would be a date and G1 would be catergories
so this formula
SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1,Raw!\$A:\$A,'Emission factors'!\$G\$1),

IF ( AND ( E1 = "NONE" , G1 = "ALL" ) ,
SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4))) ,
IF( G1="ALL" ),
SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1),
SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1,Raw!\$A:\$A,'Emission factors'!\$G\$1))

Excel Formula:
``````IF ( AND ( E1 = "NONE" , G1 = "ALL" ) , SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4))) ,
IF( G1="ALL" ),SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1),
SUMIFS(Raw!\$AF:\$AF,Raw!\$R:\$R,'Emission factors'!\$C5,Raw!\$D:\$D,'Emission factors'!E\$4,Raw!\$W:\$W,'Emission factors'!\$E\$1,Raw!\$A:\$A,'Emission factors'!\$G\$1))``````

#### darkbluecat

##### New Member
Okay so I think this works, it's what I need, the dates work but categories don't but I'll tweak it and figure it out thanks for your help

Replies
4
Views
92
Replies
1
Views
106
Replies
4
Views
83
Replies
2
Views
176
Replies
0
Views
37

1,129,498
Messages
5,636,677
Members
416,935
Latest member
Atulcp

### 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