frequency array formula with specific data array

In the attached sample file I employ the array frequency formula.

The 'data array' is a column with thousands of sorted dates (format dd.mm.yyyy) which can occur multiple times

Everything works fine, but as a matter of fact I have to hard code the 'data_array argument' but I would like to tweak the formula so that the 'data array' automatically detects the numbers that fall within the different months.

Example:
Hard-coded-Data-Array-Frequency.png
I have also attached the sample file with all the necessary data.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Frequency-EE.xlsx
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Their you go..i did the same what you are looking for but with a different formula..However it gives you the desired results of what you are looking for..

Saurabh...
Frequency-EE.xlsx
0
Andreas HermleTeam leaderAuthor Commented:
Hi Saurabh,

I am deeply impressed, wow what a formula! I will do some testing and then let you know but as things stand now, everything works to my liking. Really, this is a very professional job, great :-)

Till later, Regards, Andreas
0
Saurabh Singh TeotiaCommented:
Thanks for appreciation..You are welcome and sure let me know if you need any further help on this...

Saurabh...
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

barry houdiniCommented:
You can still use FREQUENCY function as before if you want, with an IF function on the data range so that you get the results for the required month only, e.g. this formula:

=FREQUENCY(IF($B2:$B3000-DAY($B2:$B3000)+1=E1,$A2:$A3000),$D2:$D6)

confirmed with CTRL+SHIFT+ENTER

and copied to the other columns

see attached
Frequency-barry.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andreas HermleTeam leaderAuthor Commented:
Hi Barry,

thank you very much, I will do some testing and then let you two know of the outcome

Again, thank you very much to both of you. I really appreciate it. Regards, Andreas
0
Andreas HermleTeam leaderAuthor Commented:
Hi Barry,

just checked your solution. I am very, very impressed, I just can't believe how somebody can come up with such a superb solution. Awesome, wonderful etc.

I'll do some testing on Saurabh's approach as well and then award the points, regrettably I am not able to award thousands of points ;-)
0
Andreas HermleTeam leaderAuthor Commented:
Hi Saurabh,

your solution also works just fine. Thank you very much for your superb and professional support. You both merit a 500 points award, regrettably I won't be able to do this.

Therefore I suggest splitting the points. What do you think?

To both of you: Saurabh and Barry:

I am deeply impressed by your professionalism in handling Excel Problems. This is really awesome !!! It is just fantastic to have such an expert forum at hand.
0
Andreas HermleTeam leaderAuthor Commented:
It is a pity I can only award 500 points, you both would deserve 500 points. Again, thank you very much for your professional and extraordinary support. I really appreciate it.

Regards, Andreas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.