2 IF statements needed? , lots of conditions help

darkbluecat

New Member
Joined
Jan 7, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 7, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jan 7, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Jan 7, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Jan 7, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS
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))
 
Solution

darkbluecat

New Member
Joined
Jan 7, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
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.
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