Jun 06 2018
03:34 PM
- last edited on
Jul 31 2018
08:23 AM
by
TechCommunityAP
Jun 06 2018
03:34 PM
- last edited on
Jul 31 2018
08:23 AM
by
TechCommunityAP
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
Jun 06 2018 04:13 PM
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))
Jun 06 2018 04:15 PM - edited Jun 06 2018 04:18 PM
SolutionJohn,
=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)}
Jun 06 2018 05:31 PM
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?
Jun 06 2018 05:58 PM
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?
Jun 07 2018 06:56 AM
Hi Matt,
Thank you your input. Do you know if this can be done with Frequency? My understanding is that it is much faster.
Jun 07 2018 09:55 AM
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!
Sep 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?
Sep 10 2019 02:21 PM
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.
Jun 06 2018 04:15 PM - edited Jun 06 2018 04:18 PM
SolutionJohn,
=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)}