SOLVED
Home

Frequency function for counting dates

%3CLINGO-SUB%20id%3D%22lingo-sub-201553%22%20slang%3D%22en-US%22%3EFrequency%20function%20for%20counting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201553%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20column%20that%20contains%20date%20values%20rounded%20with%20the%20INT%20function.%26nbsp%3B%20My%20understanding%20is%20that%20these%20can%20be%20counted%20using%20the%20Frequency%20function%20but%20I%20am%20not%20getting%20the%20result%20I%20expect.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20have%20an%20adjacent%20column%20indicate%20how%20many%20times%20any%20one%20of%20the%20unique%20values%20occurs%20and%20place%20that%20sum%20next%20to%20the%20first%20value.%26nbsp%3B%20Here%20is%20a%20small%20sample%20of%20data%20with%20date%20being%20the%20first%20column%2C%20and%20I%20manually%20entered%20the%20value%20in%20the%20second.%26nbsp%3B%20The%20second%20column%20is%20what%20I%20would%20like%20to%20generate%20using%20a%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20cellspacing%3D%220%22%20cellpadding%3D%220%22%20border%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E43253%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43253%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43253%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43254%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43254%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43254%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43254%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43255%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43255%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43256%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20messed%20with%20this%20function%20so%20much%20that%20I%20am%20not%20sure%20any%20of%20my%20failed%20attempts%20are%20going%20to%20be%20useful%20-%20so%20I%20am%20open%20to%20suggestions%20on%20how%20I%20can%20get%20this%20to%20work%20with%20the%20frequency%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-John%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-201553%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201877%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency%20function%20for%20counting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201877%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20John!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Detlef's%20formula%20is%20a%20Multi%20Array%20Formula%2C%20and%20you%20ignore%20it%20(Detlef's%20told%20you).%26nbsp%3B%20For%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DFREQUENCY(A2%3AA11%2CA2%3AA11)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20must%20select%20all%20the%20range%20(in%20your%20case%2C%20B2%3AB10)%2C%20put%20the%20formula%20in%20first%20cell%20(without%20losing%20the%20selection)%20and%20later%2C%20press%20CTRL%2BSHIFT%2BENTER.%26nbsp%3B%20For%20this%20Multi-Array%20formula%2C%20you%20not%20need%20to%20lock%20the%20range%20(just%20leave%20with%20relative%20reference).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20a%20single%20formula%20with%20frequency%2C%20you%20can%20use%20this%20formula%20(in%20B2%20and%20later%20drag%20it%20down%2C%20hitting%20just%20ENTER)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(FREQUENCY(A%242%3AA%2411%2CA%242%3AA%2411)%2CROWS(B%242%3AB2))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20later%20drag%20it%20down.%26nbsp%3B%20This%20purpose%20need%20to%20lock%20the%20ranges%20like%20I%20showed.%26nbsp%3B%20Check%20file%20with%20the%20two%20solutions.%26nbsp%3B%20Blessings!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201826%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency%20function%20for%20counting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201826%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Matt%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20your%20input.%26nbsp%3B%20Do%20you%20know%20if%20this%20can%20be%20done%20with%20Frequency%3F%26nbsp%3B%20My%20understanding%20is%20that%20it%20is%20much%20faster.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201574%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency%20function%20for%20counting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201574%22%20slang%3D%22en-US%22%3E%3CP%3EDetlef%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20if%20this%20is%20a%20duplicate%20post%20-%20I%20replied%20earlier%20but%20I%20don't%20think%20it%20sent.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Frequency%20Formula%20provided%20does%20not%20work%20for%20me.%26nbsp%3B%20I%20keep%20getting%20the%20number%203%20all%20of%20the%20way%20down%20the%20column%20when%20I%20copy%20the%20formula.%26nbsp%3B%20I%20did%20remember%20to%20use%20the%20array%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20as%20to%20what%20I%20am%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201572%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency%20function%20for%20counting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201572%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Detlef%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20set%20up%20everything%20just%20as%20you%20indicated%20for%20the%20Frequency%20function%2C%20however%20it%20is%20still%20not%20working.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20the%20value%203%20and%20copying%20the%20function%20to%20the%20first%20cell%2C%20copying%20it%20down%20to%20the%20other%20cells%20not%20seem%20to%20alter%20the%20values%20-%20still%203.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20my%20attempts%20included%20wrapping%20the%20frequency%20function%20in%20the%20SUM%20function%20and%20I%20used%20the%20%22--%22%20to%20ensure%20that%20true%2Ffalse%20values%20were%20converted%20to%20integer%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20what%20I%20might%20be%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201558%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency%20function%20for%20counting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201558%22%20slang%3D%22en-US%22%3E%3CP%3EJohn%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DIF(COUNTIFS(A%241%3AA1%2CA1)%3D1%2CCOUNTIFS(%24A%241%3A%24A%2410%2CA1)%2C0)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20with%20FREQUENCY()%3A%20Select%20B1%3AB10%2C%20type%20in%20the%20formula%20and%20press%20CTRL-SHIFT-ENTER.%3C%2FP%3E%3CPRE%3E%7B%3DFREQUENCY(%24A%241%3A%24A%2410%2C%24A%241%3A%24A%2410)%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201557%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency%20function%20for%20counting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201557%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20John-%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20you%20need%20frequency%20to%20get%20the%20result%20you%20would%20like%2C%20but%20of%20course%20there%20is%20always%20more%20than%20one%20way%20%22to%20skin%20a%20cat%22....%26nbsp%3B%20try%20using%20this%20formula%20and%20drag%20down%20if%20your%20example%20data%20looks%20like%20this%20(see%20attached%20.xlsx%20file%20for%20example)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(COUNTIF(A2%3A%24A%2411%2CA2)%3CCOUNTIF%3E%0A%3C%2FCOUNTIF%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20836px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F35527i68A16095C7FCF764%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22DateCount.png%22%20title%3D%22DateCount.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847159%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency%20function%20for%20counting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847159%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20looked%20at%20this%20contribution%20to%20see%20if%20would%20assist%20me%2C%20but%20it%20doesn't%20quite%20achieve%20what%20I%20would%20like%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20though%20I%20had%20done%20what%20I%20need%20to%20when%20I%20got%20the%20answer%20%228%22%20(Cell%20D6%20on%20the%20Output%20tab)%20in%20the%20attached%20file%20for%20working%20days%2C%20but%20realised%20I%20hadn't%20when%20I%20reduced%20the%20time%20between%20the%20dates.%20The%20formula%20is%20picking%20up%20the%20unique%20dates%2C%20but%20not%20between%20the%20specified%20dates.%20Can%20you%20see%20what%20I%20have%20done%20wrong%20with%20FREQUENCY%20please%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847629%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency%20function%20for%20counting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394688%22%20target%3D%22_blank%22%3E%40JNollett%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20please%20always%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fforums%2Fpostpage%2Fboard-id%2FExcelGeneral%22%20target%3D%22_self%22%3Estart%20a%20new%20conversation.%3C%2FA%3E%26nbsp%3BDo%20not%20hijack%20threads%20started%20by%20other%20users.%3C%2FP%3E%3CP%3ESecond%2C%20the%20formula%20in%20D6%20is%20an%20array%20formula.%20You%20have%20to%20enter%20it%20with%20CTRL-SHIFT-ENTER%20instead%20of%20just%20ENTER.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
John Duff
New Contributor

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. 

 

432533
432530
432530
432544
432540
432540
432540
432552
432550
432561

 

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

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

 

DateCount.png

 

Solution

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

 

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?

 

 

 

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?

 

 

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!

 

 

 

 

 

Hi @Detlef Lewin 

 

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?

@JNollett 

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