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
[TABLE="class: head"]
<tbody>[TR]
[TH] [/TH]
[TH]

M​

[/TH]
[TH]

N​

[/TH]
[TH]

O​

[/TH]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]Col B[/TD]
[TD]Col E[/TD]
[TD]Col M[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]Category[/TD]
[TD]MONTH[/TD]
[TD]Cost/Qty[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]Cat 1[/TD]
[TD]

1​

[/TD]
[TD]

$1,202​

[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]Cat 2[/TD]
[TD]

1​

[/TD]
[TD]

$431​

[/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]Cat 3[/TD]
[TD]

1​

[/TD]
[TD]

$2,404​

[/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]Cat 4[/TD]
[TD]

1​

[/TD]
[TD]

$3,625​

[/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]Cat 5[/TD]
[TD]

1​

[/TD]
[TD]

$793​

[/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]Cat 6[/TD]
[TD]

1​

[/TD]
[TD]

$1​

[/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]Cat 7[/TD]
[TD]

1​

[/TD]
[TD]

$1,162​

[/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]Cat 8[/TD]
[TD]

1​

[/TD]
[TD]

$187​

[/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD]Cat 9[/TD]
[TD]

1​

[/TD]
[TD]

$560​

[/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD]Cat 10[/TD]
[TD]

1​

[/TD]
[TD]

$4,873​

[/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD]Cat 11[/TD]
[TD]

1​

[/TD]
[TD]

$0​

[/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD]Cat 12[/TD]
[TD]

1​

[/TD]
[TD]

$71​

[/TD]
[/TR]
[TR]
[TD]

31​

[/TD]
[TD]Cat 13[/TD]
[TD]

1​

[/TD]
[TD]

$0​

[/TD]
[/TR]
[TR]
[TD]

32​

[/TD]
[TD]Cat 2[/TD]
[TD]

2​

[/TD]
[TD]

$726​

[/TD]
[/TR]
[TR]
[TD]

33​

[/TD]
[TD]Cat 3[/TD]
[TD]

2​

[/TD]
[TD]

$1,202​

[/TD]
[/TR]
[TR]
[TD]

34​

[/TD]
[TD]Cat 4[/TD]
[TD]

2​

[/TD]
[TD]

$431​

[/TD]
[/TR]
[TR]
[TD]

35​

[/TD]
[TD]Cat 5[/TD]
[TD]

2​

[/TD]
[TD]

$2,404​

[/TD]
[/TR]
[TR]
[TD]

36​

[/TD]
[TD]Cat 17[/TD]
[TD]

2​

[/TD]
[TD]

$793​

[/TD]
[/TR]
[TR]
[TD]

37​

[/TD]
[TD]Cat 19[/TD]
[TD]

2​

[/TD]
[TD]

$1​

[/TD]
[/TR]
[TR]
[TD]

38​

[/TD]
[TD]Cat 20[/TD]
[TD]

2​

[/TD]
[TD]

$900​

[/TD]
[/TR]
[TR]
[TD]

39​

[/TD]
[TD]Cat 21[/TD]
[TD]

2​

[/TD]
[TD]

$839​

[/TD]
[/TR]
[TR]
[TD]

40​

[/TD]
[TD]Cat 22[/TD]
[TD]

2​

[/TD]
[TD]

$45​

[/TD]
[/TR]
[TR]
[TD]

41​

[/TD]
[TD]Cat 3[/TD]
[TD]

2​

[/TD]
[TD]

$2,404​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Cat 3[/TD]
[TD="class: xl64, width: 64"]$6,010 [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]Cat 4[/TD]
[TD="class: xl64, width: 64"]$4,056 [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]Cat 5[/TD]
[TD="class: xl64, width: 64"]$3,197 [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]Cat 2[/TD]
[TD="class: xl64, width: 64"]$1,157 [/TD]
[/TR]
</tbody>[/TABLE]

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