- last edited on
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.
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.
06-06-2018 04:13 PM
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):
06-06-2018 04:15 PM - edited 06-06-2018 04:18 PMSolution
Or with FREQUENCY(): Select B1:B10, type in the formula and press CTRL-SHIFT-ENTER.
06-06-2018 05:31 PM
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?
06-06-2018 05:58 PM
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?
06-07-2018 06:56 AM
Thank you your input. Do you know if this can be done with Frequency? My understanding is that it is much faster.
06-07-2018 09:55 AM
The Detlef's formula is a Multi Array Formula, and you ignore it (Detlef's told you). For this formula:
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):
And later drag it down. This purpose need to lock the ranges like I showed. Check file with the two solutions. Blessings!
09-10-2019 10:19 AM
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?