1

I would like to find a formula that gives me a list of cells that contains a certain date.

Example:

name   -    day      -   amount      -    month (hidden)
a         01-01-2012     5                =month(01-01-2012) = 1
b         02-01-2012     4                =month(02-01-2012) = 1
c         10-01-2012     3                =month(10-01-2012) = 1
d         10-01-2012     6                =month(10-01-2012) = 1
e         11-02-2012     2                =month(11-02-2012) = 2

So in this example, I would like to get all the (unique) days of January (in my case a list with: 01-01-2012, 02-10-2012 & 10-01-2012). Afterwards I would like to have the total of amounts on these days of the list above (but that's easy and I guess I will find that alone :p)

I first used the Vertical Lookup formula but this gives me only one day in January, and not a list of all the days in January.

In fact it's a filter that I need, but with a formula

Thanks for your time & help

smirnoff103
  • 196
  • 1
  • 13
  • Related: [Getting unique values in Excel by using formulas only](http://stackoverflow.com/questions/1429899/getting-unique-values-in-excel-by-using-formulas-only) – Dante May Code Nov 22 '12 at 14:19
  • Column L in http://www.bumpclub.ee/~jyri_r/Excel/Answers_to_quiz_correct_wrong.xls contains a formula for unique persons list. Maybe it's suitable for your purpose, too? – Jüri Ruut Nov 22 '12 at 18:33

2 Answers2

1

For getting the list vertically:

Formula in column H of your sheet (array formula, insert with Ctrl-Shift-Enter, curled brackets inserted by Excel, not by user):

{=INDEX($B$2:$B$10;MATCH(0;COUNTIF($H$1:H1;$B$2:$B$10);0))}

Cells should be formatted as Date to get dates, not integers.

Horizontally:

{=INDEX(Data!$B$2:$B$10;MATCH(0;COUNTIF($A6:A6;Data!$B$2:$B$10);0))}

To filter out unique days in October:

=INDEX(Data!$B$2:$B$10;MATCH(0;IF(COUNTIF(A4:$A4;Data!$B$2:$B$10)=0;IF(MONTH(Data!$B$2:$B$10)=10;0;1);1);0))

Your sheet modified: http://www.bumpclub.ee/~jyri_r/Excel/filter_formula_month.xls

Jüri Ruut
  • 2,500
  • 17
  • 20
  • Hello Jüri, thanks for your answer, it looks (very) good. I didn't know about this "array formula". The only little thing I don't understand is the range in your COUNTIF that starts at H1, shouldn't it be H2 ? I tried `{=INDEX($B$2:$B$10;MATCH(0;COUNTIF($H$2:H2;$B$2:$B$10);0))}` in cell H2 but this gives me 0/01/1900 :( – smirnoff103 Nov 23 '12 at 07:54
  • The cell range referred by COUNTIF must not include the referring cell. Otherwise one gets a) circular reference and b) there would be no MATCH. The circular reference skips the whole formula giving "0", which is 0th day of the calendar. – Jüri Ruut Nov 23 '12 at 15:25
  • Ok Jüri, thanks again for your input. I tried the formula but it's still not 100%. The formula gives me 3 unique values, however there are much more unique dates, I upload my xls sheet where the formula should be used, [link](http://smirnoff103.com/_dump/filter_formula.xls) . Thank you – smirnoff103 Nov 26 '12 at 07:56
  • I checked the formula, seemed to work. I updated your sheet: http://www.bumpclub.ee/~jyri_r/Excel/filter_formula.xls, there are 7 unique values listed. – Jüri Ruut Nov 26 '12 at 20:26
  • No you have open the wrong XLS sheet, I mean this one : http://smirnoff103.com/_dump/filter_formula.xls – smirnoff103 Nov 27 '12 at 15:23
  • Thanks a lot again Jüri, we're almost there :) I would like that the function now only filters date in a certain month, I tried already but it's (obviously) not working :p [here is the file:](http://smirnoff103.Com/_dump/filter_formula_month.xls) , it's REALLY the last time I annoy you, then everything is done :) tnx for your help – smirnoff103 Nov 28 '12 at 10:49
  • I guess that now we are completely there :-) – Jüri Ruut Nov 28 '12 at 22:22
0

Select the cell you want the result to start appearing in.

Choose the Data ribbon and under filter you will find advanced

Choose the dates you want filtered, and at the bottom of that dialogue you'll find a checkbox with Unique Items only, check that one.

And Bob's youre uncle

Archlight
  • 2,019
  • 2
  • 21
  • 34
  • Thanks for your fast answer but that's not what I want. But I don't want to use filters, because the first tab (where all the data can be found) should always be "full" (so no filters active). The data I want to use is for another tab in Excel that shows all the data without filters – smirnoff103 Nov 22 '12 at 15:12