Excel Dropdown List (Data Validation)

[nataliek92]

Hi everyone,

I have a drop down list in, say, sheet 1 cell A1. This list contains some dates: 01/03/2015, 02/03/2015, 03/03/2015. I have another sheet (sheet 2) with some data. In sheet 1 I have some tables which summarise the data based on the value in cell A1. One formula I am using is this:

=SUMIFS(Sheet2!$G:$G,Sheet2!$A:$A,Sheet1!$A$1)

(this formula calculates the sum of column G (sheet 2) where column A (sheet 2) equals cell A1 (sheet 1))

This code works fine.

However, I would like a drop down value which will show the sum across all dates. Is this possible?

I have tried using * as such:

=SUMIFS(Sheet2!$G:$G,Sheet2!$A:$A,"*"&Sheet1!$A$1&"*")

and adding various different things to my drop down list, like: 2015, 0, /, etc. as this normally works with text drop down lists.

But the above gives 0's, when it should not. Does anyone know why this doesn't work? I believe it will be something to do with the date formatting. I know excel isn't really a fan of dates....

Does anyone know how I can create an "Overall" drop down variable as such? It doesn't have to be anything too fancy for the time being... I would just like a working selection list.

Any help would be very much appreciated!!

 

[Shrikant]

You may Add one more entry in Drop Down list say "Overall"

Now you may use this

=SUMIFS(Sheet2!$G:$G,Sheet2!$A:$A,IF(Sheet1!$A$1="overall",">0",Sheet1!$A$1)

 

[nataliek92]

You may Add one more entry in Drop Down list say "Overall"

Now you may use this

=SUMIFS(Sheet2!$G:$G,Sheet2!$A:$A,IF(Sheet1!$A$1="overall",">0",Sheet1!$A$1)

Thank you so much! Such a simple fix, can't believe I didn't think of this myself to be honest!

Works perfectly. Thanks again