We help IT Professionals succeed at work.

frequency array formula with specific data array

Andreas Hermle
on
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
Comment
Watch Question

Top Expert 2015
Commented:
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
Andreas HermleTeam leader

Author

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
Top Expert 2015

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

Saurabh...
Most Valuable Expert 2013
Commented:
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
Andreas HermleTeam leader

Author

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
Andreas HermleTeam leader

Author

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 ;-)
Andreas HermleTeam leader

Author

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.
Andreas HermleTeam leader

Author

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