3

Having a list of days suchs as:

01-giu-16
01-giu-16
01-giu-16
31-mag-16
31-mag-16
31-mag-16
31-mag-16
30-mag-16

I was looking for an excel formula that helps me count the number of unique days in the list (in this example 3)

Moreover I need the count only for the dates which have a specific ID in the next column (for example 1565)

Noomak
  • 371
  • 5
  • 19
  • 1st question, are the dates stored as excel date time serials? when you change the cell format to general do they show a number like 4265? or if you prefer, `=isnumber(A1)` , where A1 is one of the dates, does it return true? – Forward Ed Jun 10 '16 at 08:08
  • Will your dates always be grouped or sorted? – Forward Ed Jun 10 '16 at 08:09
  • not grouped/sorted list and they are cell number – Noomak Jun 10 '16 at 08:13
  • There is a big difference between sorting dates as you have show in your example and sorting and counting text or numbers. – Forward Ed Jun 10 '16 at 08:15

2 Answers2

3

Without any additional criteria, you can achieve the uniqueness count by using

=SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8)), assuming your data are in the range A1:A8.

To evaluate subject to additional criteria (suppose they are in column B), use

{=SUM(--(FREQUENCY(IF(B1:B8=1565,MATCH(A1:A8,A1:A8,0)),ROW(A1:A8)-ROW(A1)+1)>0))}

This is an array formula: use Ctrl + Shift + Return once you're done editing (and don't type the curly braces yourself). Personally though I think this exceeds the reasonable threshold for complexity: I'd be inclined to adopt the first approach on a column that represents an intermediate transformation of your input data.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
1

Lets assume your data is in Column A and it has a header row. So the first data number will actually be in A2. Place this formula in B2 and copy down beside your list. It will generate a list of unique cell numbers from column A. Once you have the list you simply need to use a function to count the side of it.

=iferror(INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0,0),0)),"")

in C2 you can use the following formula to get the number of unique cell numbers

=COUNTA(B2:B9)-COUNTIF(B2:B9,"")

In D2 you can use the following formula to get the count of each unique cell number from your original list. Copy it down as far as you need to go.

=IF(B5="","",COUNTIF($A$2:$A$9,B5))
Forward Ed
  • 9,484
  • 3
  • 22
  • 52