Forum Discussion
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)}
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))
Hi Matt,
Thank you your input. Do you know if this can be done with Frequency? My understanding is that it is much faster.
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!
