drop down date selection IF statement

[darkbluecat]

So I have a drop down I just made which has loads of different mmm-yy selections and a "none" selection (which produces just the total)

=IF($E$1="mmm-yy",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))

The second sumifs on it's own works but excel won't recognise the drop down list when I change it it's always just the total which I only want the total overall when the none option is selected. I've formatted the dropdown cell to mmm-yy and the data it's from is mmm-yy format. Raw is the raw data sheet and emission factors refers to the sheet I'm working on, could it be because the dates I used to make the dropdown are separate in a different sheet for reference and not in the raw data sheet?

Can't provide anymore info as it's for work

 

[etaf]

this part of the IF
=IF($E$1="mmm-yy"
is looking for the actual TEXT MMM-YY
and NOT a date

if you have NONE in the dropdown
How about
=IF ( $E$1 <> "NONE",

Or change the 2 formulas around and use
=IF ( $E$1 = "NONE",

=IF($E$1="NONE", SUMIFS(Raw!$AF:$AF,Raw!$R:$R,'Emission factors'!$C5,Raw!$D:$D,'Emission factors'!E$4),
SUMIFS(Raw!$AF:$AF,Raw!$R:$R,'Emission factors'!$C5,Raw!$D:$D,'Emission factors'!E$4,Raw!$W:$W,'Emission factors'!$E$1))

OR

=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),SUMIFS(Raw!$AF:$AF,Raw!$R:$R,'Emission factors'!$C5,Raw!$D:$D,'Emission factors'!E$4))

 

[darkbluecat]

this part of the IF
=IF($E$1="mmm-yy"
is looking for the actual TEXT MMM-YY
and NOT a date

if you have NONE in the dropdown
How about
=IF ( $E$1 <> "NONE",

Or change the 2 formulas around and use
=IF ( $E$1 = "NONE",

=IF($E$1="NONE", SUMIFS(Raw!$AF:$AF,Raw!$R:$R,'Emission factors'!$C5,Raw!$D:$D,'Emission factors'!E$4),
SUMIFS(Raw!$AF:$AF,Raw!$R:$R,'Emission factors'!$C5,Raw!$D:$D,'Emission factors'!E$4,Raw!$W:$W,'Emission factors'!$E$1))

OR

=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),SUMIFS(Raw!$AF:$AF,Raw!$R:$R,'Emission factors'!$C5,Raw!$D:$D,'Emission factors'!E$4))

I tried changing the two formulas around before and that didn't work I'll try the first option

 

[darkbluecat]

this part of the IF
=IF($E$1="mmm-yy"
is looking for the actual TEXT MMM-YY
and NOT a date

if you have NONE in the dropdown
How about
=IF ( $E$1 <> "NONE",

Or change the 2 formulas around and use
=IF ( $E$1 = "NONE",

=IF($E$1="NONE", SUMIFS(Raw!$AF:$AF,Raw!$R:$R,'Emission factors'!$C5,Raw!$D:$D,'Emission factors'!E$4),
SUMIFS(Raw!$AF:$AF,Raw!$R:$R,'Emission factors'!$C5,Raw!$D:$D,'Emission factors'!E$4,Raw!$W:$W,'Emission factors'!$E$1))

OR

=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),SUMIFS(Raw!$AF:$AF,Raw!$R:$R,'Emission factors'!$C5,Raw!$D:$D,'Emission factors'!E$4))

The first option worked THANK YOU!!

 

[etaf]

you are welcome