Forum Discussion

John Duff's avatar
John Duff
Copper Contributor
Jun 06, 2018
Solved

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.   ...
Excel Desktop
Formulas & Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Jun 06, 2018

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

     

John Duff's avatar
John Duff
Copper Contributor
Jun 07, 2018

Hi Matt,

 

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

John Jairo Vergara Domínguez's avatar
John Jairo Vergara Domínguez
Brass Contributor
Jun 07, 2018

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!

 

 

 

 

 

Resources