# drop down date selection IF statement

#### darkbluecat

##### New Member
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?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### etaf

##### Well-known Member
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
=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

##### New Member
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
=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
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
=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!!

you are welcome

Replies
10
Views
127
Replies
4
Views
84
Replies
5
Views
237
Replies
1
Views
180
Replies
13
Views
229

1,129,795
Messages
5,638,367
Members
417,023
Latest member
Zimbo38

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