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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,575
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,575
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,127,212
Messages
5,623,420
Members
415,974
Latest member
ZorroOP

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