drop down date selection IF statement

darkbluecat

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

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
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))
 
Solution

darkbluecat

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

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

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,127,707
Messages
5,626,404
Members
416,182
Latest member
elija95

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