Frequency function for counting dates

[John Duff]

Hi,

I have a column that contains date values rounded with the INT function.  My understanding is that these can be counted using the Frequency function but I am not getting the result I expect. 

I would like to have an adjacent column indicate how many times any one of the unique values occurs and place that sum next to the first value.  Here is a small sample of data with date being the first column, and I manually entered the value in the second.  The second column is what I would like to generate using a formula. 

43253	3
43253	0
43253	0
43254	4
43254	0
43254	0
43254	0
43255	2
43255	0
43256	1

I've messed with this function so much that I am not sure any of my failed attempts are going to be useful - so I am open to suggestions on how I can get this to work with the frequency function. 

Thank you!

-John

[#2]

Hey John-

Not sure you need frequency to get the result you would like, but of course there is always more than one way "to skin a cat"....  try using this formula and drag down if your example data looks like this (see attached .xlsx file for example):

=IF(COUNTIF(A2:$A$11,A2)<COUNTIF($A$2:$A$11,A2),0,COUNTIF(A2:$A$11,A2))

[#3 · Accepted solution]

John,

=IF(COUNTIFS(A$1:A1,A1)=1,COUNTIFS($A$1:$A$10,A1),0)

Or with FREQUENCY(): Select B1:B10, type in the formula and press CTRL-SHIFT-ENTER.

{=FREQUENCY($A$1:$A$10,$A$1:$A$10)}

[#4]

Hi Detlef,

I set up everything just as you indicated for the Frequency function, however it is still not working. 

I get the value 3 and copying the function to the first cell, copying it down to the other cells not seem to alter the values - still 3.

One of my attempts included wrapping the frequency function in the SUM function and I used the "--" to ensure that true/false values were converted to integer data. 

Any idea what I might be doing wrong?

[#5]

Detlef,

Sorry if this is a duplicate post - I replied earlier but I don't think it sent. 

The Frequency Formula provided does not work for me.  I keep getting the number 3 all of the way down the column when I copy the formula.  I did remember to use the array formula. 

Any ideas as to what I am doing wrong?

[#6]

Hi Matt,

Thank you your input.  Do you know if this can be done with Frequency?  My understanding is that it is much faster. 

[#7]

Hi, John!

The Detlef's formula is a Multi Array Formula, and you ignore it (Detlef's told you).  For this formula:

=FREQUENCY(A2:A11,A2:A11)

You must select all the range (in your case, B2:B10), put the formula in first cell (without losing the selection) and later, press CTRL+SHIFT+ENTER.  For this Multi-Array formula, you not need to lock the range (just leave with relative reference).

If you want a single formula with frequency, you can use this formula (in B2 and later drag it down, hitting just ENTER):

=INDEX(FREQUENCY(A$2:A$11,A$2:A$11),ROWS(B$2:B2))

And later drag it down.  This purpose need to lock the ranges like I showed.  Check file with the two solutions.  Blessings!

[#8]

Hi @Detlef Lewin 

I have looked at this contribution to see if would assist me, but it doesn't quite achieve what I would like to do.

I though I had done what I need to when I got the answer "8" (Cell D6 on the Output tab) in the attached file for working days, but realised I hadn't when I reduced the time between the dates. The formula is picking up the unique dates, but not between the specified dates. Can you see what I have done wrong with FREQUENCY please?

[#9]

@JNollett 

First, please always start a new conversation. Do not hijack threads started by other users.

Second, the formula in D6 is an array formula. You have to enter it with CTRL-SHIFT-ENTER instead of just ENTER.