Index Match Large Frequency formula

[bsquad]

I am looking to add something like a FREQUENCY criteria to this

=IFERROR(INDEX($B$2:$B$5000,MATCH(SUMPRODUCT(LARGE(($E$2:$E$5000=1)*($M$2:$M$5000),ROWS($A$1:A1))),IF(($E$2:$E$5000=1),$M$2:$M$5000),0)),"")

I have my index categories in Col B, and my LARGE in Col M, Col E is just a Month - if its equal to a Month number. I would like to add a FREQUENCY criteria to my index categories (Col B). i.e. if the category/text occurs 4 times then do the index-match-large.
FREQUENCY or COUNTIF(s)

 

[Aladin Akyurek]

Care to describe what you want to solve in words along with an input sample and the output based this input sample?

 

[bsquad]

absolutely

so basically in the table below it would Index Cat 3 first since it has a count of 3 occurrences (most out of any of them) , next it would be Cat 4 then 5, then 2, since all 3 have 2 occurrences and if you were to sum by category and take the largest first it would be Cat 4, 5, 2. Its basically a smart filter or something like a pivot.

I must have an outdated Forum tools but ignore the first row and the first column
Excel 2013 32 bit

	M​	N​	O​
17​	Col B	Col E	Col M
18​	Category	MONTH	Cost/Qty
19​	Cat 1	1​	$1,202​
20​	Cat 2	1​	$431​
21​	Cat 3	1​	$2,404​
22​	Cat 4	1​	$3,625​
23​	Cat 5	1​	$793​
24​	Cat 6	1​	$1​
25​	Cat 7	1​	$1,162​
26​	Cat 8	1​	$187​
27​	Cat 9	1​	$560​
28​	Cat 10	1​	$4,873​
29​	Cat 11	1​	$0​
30​	Cat 12	1​	$71​
31​	Cat 13	1​	$0​
32​	Cat 2	2​	$726​
33​	Cat 3	2​	$1,202​
34​	Cat 4	2​	$431​
35​	Cat 5	2​	$2,404​
36​	Cat 17	2​	$793​
37​	Cat 19	2​	$1​
38​	Cat 20	2​	$900​
39​	Cat 21	2​	$839​
40​	Cat 22	2​	$45​
41​	Cat 3	2​	$2,404​

<tbody>
</tbody>

Sheet: Sheet2

<tbody>
</tbody>

 

[Aladin Akyurek]

Ok, this is your input.

Book1
	A	B	C
1	Category	MONTH	Cost/Qty
2	Cat 1	1	$1,202
3	Cat 2	1	$431
4	Cat 3	1	$2,404
5	Cat 4	1	$3,625
6	Cat 5	1	$793
7	Cat 6	1	$1
8	Cat 7	1	$1,162
9	Cat 8	1	$187
10	Cat 9	1	$560
11	Cat 10	1	$4,873
12	Cat 11	1	$0
13	Cat 12	1	$71
14	Cat 13	1	$0
15	Cat 2	2	$726
16	Cat 3	2	$1,202
17	Cat 4	2	$431
18	Cat 5	2	$2,404
19	Cat 17	2	$793
20	Cat 19	2	$1
21	Cat 20	2	$900
22	Cat 21	2	$839
23	Cat 22	2	$45
24	Cat 3	2	$2,404
Sheet1

Care to post the output which must obtain the above input?

 

[bsquad]

yes sir, that would be it

 

[Aladin Akyurek]

yes sir, that would be it

Sorry, isn't that the input? If it's, care also to post the output that must obtain from that input?

 

[bsquad]

Yes, that was the input.

Here is the output

Cat 3	$6,010
Cat 4	$4,056
Cat 5	$3,197
Cat 2	$1,157

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

I am not really looking for the 2nd column with the dollar amounts, thats just so you could visually see the logic for my desired order.
>>its like a hierarchy order of the count of categories, then by total dollar amount, if that makes sense