• 408K Members
• 7,774 Online
• 465K Conversations
SOLVED

New Contributor

# Frequency function for counting dates

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

8 Replies

# Re: Frequency function for counting dates

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

Solution

# Re: Frequency function for counting dates

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

# Re: Frequency function for counting dates

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?

# Re: Frequency function for counting dates

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?

# Re: Frequency function for counting dates

Hi Matt,

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

# Re: Frequency function for counting dates

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!

# Re: Frequency function for counting dates

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?

# Re: Frequency function for counting dates

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies