Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 408K Members
- 7,774 Online
- 465K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Frequency function for counting dates

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
## Frequency function for counting dates

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

John Duff

New Contributor

06-06-2018
03:34 PM
- last edited on
07-31-2018
08:23 AM
by
TechCommunityAP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-06-2018
03:34 PM
- last edited on
07-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

Labels:

8 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-06-2018 04:15 PM - edited 06-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)}

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-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.

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
21.9K
Views

1 Likes

35 Replies

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
20.9K
Views

9 Likes

11 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
133K
Views

6 Likes

28 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
10.9K
Views

0 Likes

9 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
21.9K
Views

0 Likes

7 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft