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?
 
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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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

Saurabh...
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.