Formula list the most frequently occuring numbers in an expanding array

Hi All,

I'm struggling so far without any solution to find a way to use this formula to list the most frequently occuring numbers with multiple IF criteria:

Please Login or Register  to view this content.

That formula list the most frequently occuring numbers in an expanding array as explained at this link:
exceljet.net/formula/list-most-frequently-occuring-numbers

I'm new here so I'm not sure if I'm allowed to post a link to another website here so if that's against the rules I apologize. It's just that it's must easier to explain my problem by referrencing that link.

Anyway, my problem is how can I add multiple criteria to the formula as I did in the formula below.

Please Login or Register  to view this content.

The problem with the formula above is that it only results in reporting the most frequently occurring number, instead of the most frequent followed by the second most frequent, then the third most frequent, etc as the first formula does. But how to add these multiple IF criteria to the MODE(IF(1-ISNUMBER(MATCH function?

Please Login or Register  to view this content.

I left out the IF(Q12:Q1000<>"") condition because I think that IF(1-ISNUMBER) should take care of that.

So I tried this:

Please Login or Register  to view this content.

But that did not work so where should I insert the additional IF conditions? Can anyone help me solve this? Thanks.

Also, what is the formula to list the number of times each frequently occurring number occurs as shown in the Count column of the example at the link I posted? Any ideas anyone? Thanks again.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. You are new, so I did it for you this time.)

Instructions about attaching workbooks are at the top of the page.

I changed your title and explained why - do NOT re-edit something that a moderator has changed. Thanks.

Please provide a sample workbook - instructions at the top of the page.

*Untested*

In D16 (array-entered)

=IFERROR(MODE(IF(score>threshold,IF(type=selectedtype,IF(list<>"",IF(COUNTIF(D$15:D15,list)={0},list))))),"")

Adapt for other ranges accordingly

@zzzeddy
The MODE function returns values only if repeated i.e. occurring at least twice, which, I reckon the formula I provided achieves. The other values as listed in your post occur just once after meeting the criteria, hence are not listed.

@ globally
Is my understanding of your requirements correct?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

@zzzeddy

Interesting proposition. I'm afraid, I reckon we need a helper column to get what you desire in order to ascertain which three values were encountered earlier, since the MODE function is not capable of handling that.

See attached an abbreviated mock-up of what you were suggesting. The helper column is Col. F and the final output is Col. G. I also interchanged the 43 and 78 in the list to suit what you were suggesting and ensure it works at all times

formula-list-the-most-frequently-occuring-numbers-in-an-expanding-array-samplemostfreqnums.xlsx

P.S. thanks for the rep

Hi All,

I attached an expanded version of the original sample workbook in this thread with all the working suggestions above included. This expanded workbook contains other features not related to this thread so I won't discuss them here, but if you want to understand the details of those additonal features you can go to the following threads where they are discussed:

Formula to selectively find the total score based on multiple criteria from 3 lists and
Use Solver Add-in to find the highest Total Score by selecting deselecting filter lists

My question now is about the formulas to the right of the attached SampleFreqNumsExpanded.xlsx workbook that are used to find most frequent numbers in Expanding Arrays with Multiple Criteria, under the heading "Expanding Arrays with Multiple Criteria" and more specifically about the formula in cell O21 under the heading, "Best Top Numbers Formula". That formula is an expanding array formula that is first entered into cell O21 then filled or copied down to the cells directly below O21 up to cell O29.

Since all the 3 Top Numbers array formulas are expanding array formulas, for them to work, they must be entered with Control + Shift + Enter.

The "Best Top Numbers Formula" in cell O21 is as follows:

Formula:

Please Login or Register  to view this content.

So my question for all the experts here is how, preferably without adding any extra helper columns, can I limit this expanding array to ONLY capture those most frequently occurring numbers that appear 3 times or more in list1. I called the minimum number of times those most frequently occurring numbers must occur to qualify for inclusion the expanding array the Freq Threshold and that threshold number is in cell M3 which I called the range named, freqthreshold. So the list of most frequently occurring numbers must only contain numbers whose frequency of occurrence in list1 is >= freqthreshold. So how can I add this additional freqthreshold criteria to the expanding array formula above from cell O21 that is copied down to cell O29? Can anyone help with this? Thank you very much for your help.

EDIT: I added this question to its own new thread here: How to Capture ONLY the most frequent numbers whose occurrence >=3 in a list?