CODEWITHSUNDEEP
excellistdateformuladays
Noomak
Noomak

Reputation: 371

Excel - How to count unique days in a list a duplicated days

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)

Upvotes: 3

Views: 5562

Answers (2)

Bathsheba
Bathsheba

Reputation: 234635

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.

Upvotes: 3

Forward Ed
Forward Ed

Reputation: 9874

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

Upvotes: 1

Related Questions