Index & Match with Sumproduct for repeated values

[Amenendez]

Dear All,

I have the following data list with two main fields (Category and Country) for Year 1 and Year 2, that I want to get indexed in order from max to min, adding up the values for those categories repeated in the below table.

I would like to have i.e. For Year 1 and Country F the list from Max to Min of the Categories with the sum of the value.

I.e.(The list shall return me That Category C19 is the first one with 1,952 USD, followed by C11 with USD 480,...)

Can anyone support me with a formula for this, without using a Pivot Table?

Thanks

	A	B	C	D	E
1	Category	Country	Year1	Year 2	Total
2	C01	F	0.5	0.4	1.0
3	C01	F	0.0	0.2	0.2
4	C01	F	7.6	4.8	12.4
5	C02	F	0.8	29.4	30.3
6	C02	F	22.5	35.0	57.5
7	C03	F	0.2	79.6	79.8
8	C04	F	1.1	0.2	1.3
9	C05	F	0.0	0.0	0.0
10	C06	F	51.0	14.4	65.4
11	C20	F	28.2	29.8	58.0
12	C20	F	1.5	1.9	3.4
13	C20	F	104.5	145.8	250.3
14	C20	F	82.4	59.9	142.3
15	C07	F	0.1	0.1	0.2
16	C07	F	21.6	16.6	38.2
17	C08	F	1.8	1.5	3.3
18	C09	F	51.4	40.0	91.5
19	C10	F	42.0	50.8	92.8
20	C10	F	0.1	0.3	0.5
21	C10	F	3.2	1.5	4.7
22	C11	F	480.0	272.0	752.0
23	C12	F	17.5	27.7	45.2
24	C12	F	0.3	0.3	0.6
25	C12	F	69.6	22.7	92.3
26	C12	F	59.0	55.1	114.1
27	C12	F	73.0	73.3	146.3
28	C13	F	128.5	111.2	239.8
29	C14	F	34.6	24.4	59.0
30	C15	F	0.0	0.1	0.2
31	C16	F	44.2	11.5	55.7
32	C17	F	0.0	15.4	15.4
33	C19	F	391.0	399.2	790.2
34	C19	F	814.4	578.4	1,392.7
35	C19	F	394.1	649.5	1,043.6
36	C19	F	102.5	133.3	235.8
37	C19	F	250.9	245.6	496.6
38	C18	F	0.9	0.6	1.5
39	C01	A	0.5	0.4	1.0
40	C01	B	0.0	0.2	0.2
41	C01	C	7.6	4.8	12.4
42	C02	E	0.8	29.4	30.3
43	C02	C	22.5	35.0	57.5
44	C03	C	0.2	79.6	79.8
45	C04	A	1.1	0.2	1.3
46	C05	C	0.0	0.0	0.0
47	C06	A	51.0	14.4	65.4
48	C20	A	28.2	29.8	58.0
49	C20	B	1.5	1.9	3.4
50	C20	D	104.5	145.8	250.3
51	C20	C	82.4	59.9	142.3
52	C07	B	0.1	0.1	0.2
53	C07	C	21.6	16.6	38.2
54	C08	C	1.8	1.5	3.3
55	C09	C	51.4	40.0	91.5
56	C10	A	42.0	50.8	92.8
57	C10	B	0.1	0.3	0.5
58	C10	C	3.2	1.5	4.7
59	C11	A	480.0	272.0	752.0
60	C12	A	17.5	27.7	45.2
61	C12	B	0.3	0.3	0.6
62	C12	D	69.6	22.7	92.3
63	C12	E	59.0	55.1	114.1
64	C12	C	73.0	73.3	146.3
65	C13	C	128.5	111.2	239.8
66	C14	A	34.6	24.4	59.0
67	C15	C	0.0	0.1	0.2
68	C16	A	44.2	11.5	55.7
69	C17	C	0.0	15.4	15.4
70	C19	A	391.0	399.2	790.2
71	C19	B	814.4	578.4	1,392.7
72	C19	D	394.1	649.5	1,043.6
73	C19	E	102.5	133.3	235.8
74	C19	C	250.9	245.6	496.6
75	C18	A	0.9	0.6	1.5
76	Grand Total		3,281.3	3,132.6	6,413.9
77
78
79
80	Select Year	Year 1
81
82	Select Country	F
83
84
85
86	Category	USD	%	%
87	Category in Order 1	USD sum for the category in Order 1
88	Category in Order 2	USD sum for the category in Order 2		0.0%
89	Category in Order 3	USD sum for the category in Order 3		0.0%
90	Category in Order 4	USD sum for the category in Order 4		0.0%
91	Category in Order 5	USD sum for the category in Order 5		0.0%
92	Category in Order 6	USD sum for the category in Order 6		0.0%
93	Category in Order 7	USD sum for the category in Order 7		0.0%
94	Category in Order 8	USD sum for the category in Order 8		0.0%
95	Category in Order 9	USD sum for the category in Order 9		0.0%
96	Category in Order 10	USD sum for the category in Order 10		0.0%
97	Category in Order 11	USD sum for the category in Order 11		0.0%
98	Category in Order 12	USD sum for the category in Order 12		0.0%
99	Category in Order 13	USD sum for the category in Order 13		0.0%
100	Category in Order 14	USD sum for the category in Order 14		0.0%
101	Category in Order 15	USD sum for the category in Order 15		0.0%
102	Category in Order 16	USD sum for the category in Order 16		0.0%
103	Category in Order 17	USD sum for the category in Order 17		0.0%
104	Category in Order 18	USD sum for the category in Order 18		0.0%
105	Category in Order 19	USD sum for the category in Order 19		0.0%
124	Total		0.0%	-

 

[jasonb75]

Can anyone support me with a formula for this, without using a Pivot Table?

It could be done in a reasonably practical manner using formulas to show the results in order of the original list, but not to sort by subtotal.
If you want MAX to MIN by sum then you would need to use a pivot table or possibly power query, but not formulas.

 

[Amenendez]

Many thanks Jason

 

[Amenendez]

Thanks Jason, I know how to sort individual values but I could not find a way to sort by subtotal.
Much appreciated, anyway

 

[Fluff]

If you are happy with helper columns & have Dynamic array functions, how about

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Category	Country	Year1	Year 2	Total			Cat	Total		Cat	Total
2	C01	F	0.5	0.4	1		F	C01	8.1		C19	1952.9
3	C01	F	0	0.2	0.2		Year1	C02	23.3		C11	480
4	C01	F	7.6	4.8	12.4			C03	0.2		C12	219.4
5	C02	F	0.8	29.4	30.3			C04	1.1		C20	216.6
6	C02	F	22.5	35	57.5			C05	0		C13	128.5
7	C03	F	0.2	79.6	79.8			C06	51		C09	51.4
8	C04	F	1.1	0.2	1.3			C20	216.6		C06	51
9	C05	F	0	0	0			C07	21.7		C10	45.3
10	C06	F	51	14.4	65.4			C08	1.8		C16	44.2
11	C20	F	28.2	29.8	58			C09	51.4		C14	34.6
12	C20	F	1.5	1.9	3.4			C10	45.3		C02	23.3
13	C20	F	104.5	145.8	250.3			C11	480		C07	21.7
14	C20	F	82.4	59.9	142.3			C12	219.4		C01	8.1
15	C07	F	0.1	0.1	0.2			C13	128.5		C08	1.8
16	C07	F	21.6	16.6	38.2			C14	34.6		C04	1.1
17	C08	F	1.8	1.5	3.3			C15	0		C18	0.9
18	C09	F	51.4	40	91.5			C16	44.2		C03	0.2
19	C10	F	42	50.8	92.8			C17	0		C05	0
20	C10	F	0.1	0.3	0.5			C19	1952.9		C15	0
21	C10	F	3.2	1.5	4.7			C18	0.9		C17	0
22	C11	F	480	272	752
23	C12	F	17.5	27.7	45.2
24	C12	F	0.3	0.3	0.6
25	C12	F	69.6	22.7	92.3
26	C12	F	59	55.1	114.1
27	C12	F	73	73.3	146.3
28	C13	F	128.5	111.2	239.8
29	C14	F	34.6	24.4	59
30	C15	F	0	0.1	0.2
31	C16	F	44.2	11.5	55.7
32	C17	F	0	15.4	15.4
33	C19	F	391	399.2	790.2
34	C19	F	814.4	578.4	1,392.70
35	C19	F	394.1	649.5	1,043.60
36	C19	F	102.5	133.3	235.8
37	C19	F	250.9	245.6	496.6
38	C18	F	0.9	0.6	1.5
39	C01	A	0.5	0.4	1
40	C01	B	0	0.2	0.2
41	C01	C	7.6	4.8	12.4
42	C02	E	0.8	29.4	30.3
43	C02	C	22.5	35	57.5
44	C03	C	0.2	79.6	79.8
45	C04	A	1.1	0.2	1.3
46	C05	C	0	0	0
47	C06	A	51	14.4	65.4
48	C20	A	28.2	29.8	58
49	C20	B	1.5	1.9	3.4
50	C20	D	104.5	145.8	250.3
51	C20	C	82.4	59.9	142.3
52	C07	B	0.1	0.1	0.2
53	C07	C	21.6	16.6	38.2
54	C08	C	1.8	1.5	3.3
55	C09	C	51.4	40	91.5
56	C10	A	42	50.8	92.8
57	C10	B	0.1	0.3	0.5
58	C10	C	3.2	1.5	4.7
59	C11	A	480	272	752
60	C12	A	17.5	27.7	45.2
61	C12	B	0.3	0.3	0.6
62	C12	D	69.6	22.7	92.3
63	C12	E	59	55.1	114.1
64	C12	C	73	73.3	146.3
65	C13	C	128.5	111.2	239.8
66	C14	A	34.6	24.4	59
67	C15	C	0	0.1	0.2
68	C16	A	44.2	11.5	55.7
69	C17	C	0	15.4	15.4
70	C19	A	391	399.2	790.2
71	C19	B	814.4	578.4	1,392.70
72	C19	D	394.1	649.5	1,043.60
73	C19	E	102.5	133.3	235.8
74	C19	C	250.9	245.6	496.6
75	C18	A	0.9	0.6	1.5
76	Grand Total		3,281.30	3,132.60	6,413.90
Data

Cell Formulas
Range	Formula
H2:H21	H2	=UNIQUE(FILTER(A2:A75,B2:B75=G2))
I2:I21	I2	=SUMIFS(INDEX(C2:D75,0,MATCH(G3,C1:D1,0)),A2:A75,H2#,B2:B75,G2)
K2:L21	K2	=SORT(H2#:I2,2,-1)
Dynamic array formulas.

 

[jasonb75]

I've been trying a few things without the new UNIQUE, FILTER and SORT functions, this works with the sample, but not sure how well it would handle a much larger volume of data.

Book12345 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	Category	Country	Year1	Year 2	Total		Select Year	Year1
2	C01	F	0.5	0.4	1
3	C01	F	0	0.2	0.2		Select Country	f
4	C01	F	7.6	4.8	12.4
5	C02	F	0.8	29.4	30.3
6	C02	F	22.5	35	57.5
7	C03	F	0.2	79.6	79.8		Category	USD	%
8	C04	F	1.1	0.2	1.3		C19	1952.9	63.946%
9	C05	F	0	0	0		C11	480	15.717%
10	C06	F	51	14.4	65.4		C12	219.4	7.184%
11	C20	F	28.2	29.8	58		C20	216.6	7.092%
12	C20	F	1.5	1.9	3.4		C06	51	1.670%
13	C20	F	104.5	145.8	250.3		C10	45.3	1.483%
14	C20	F	82.4	59.9	142.3		C14	34.6	1.133%
15	C07	F	0.1	0.1	0.2		C02	23.3	0.763%
16	C07	F	21.6	16.6	38.2		C07	21.7	0.711%
17	C08	F	1.8	1.5	3.3		C01	8.1	0.265%
18	C09	F	51.4	40	91.5		C18	0.9	0.029%
19	C10	F	42	50.8	92.8		C03	0.2	0.007%
20	C10	F	0.1	0.3	0.5		C17	0	0.000%
21	C10	F	3.2	1.5	4.7		C15	0	0.000%
22	C11	F	480	272	752
23	C12	F	17.5	27.7	45.2
24	C12	F	0.3	0.3	0.6
25	C12	F	69.6	22.7	92.3
26	C12	F	59	55.1	114.1
27	C12	F	73	73.3	146.3		Total	3054	100.00%
28	C13	F	128.5	111.2	239.8
29	C14	F	34.6	24.4	59
30	C15	F	0	0.1	0.2
31	C16	F	44.2	11.5	55.7
32	C17	F	0	15.4	15.4
33	C19	F	391	399.2	790.2
34	C19	F	814.4	578.4	1,392.70
35	C19	F	394.1	649.5	1,043.60
36	C19	F	102.5	133.3	235.8
37	C19	F	250.9	245.6	496.6
38	C18	F	0.9	0.6	1.5
39	C01	A	0.5	0.4	1
40	C01	B	0	0.2	0.2
41	C01	C	7.6	4.8	12.4
42	C02	E	0.8	29.4	30.3
43	C02	C	22.5	35	57.5
44	C03	C	0.2	79.6	79.8
45	C04	A	1.1	0.2	1.3
46	C05	C	0	0	0
47	C06	A	51	14.4	65.4
48	C20	A	28.2	29.8	58
49	C20	B	1.5	1.9	3.4
50	C20	D	104.5	145.8	250.3
51	C20	C	82.4	59.9	142.3
52	C07	B	0.1	0.1	0.2
53	C07	C	21.6	16.6	38.2
54	C08	C	1.8	1.5	3.3
55	C09	C	51.4	40	91.5
56	C10	A	42	50.8	92.8
57	C10	B	0.1	0.3	0.5
58	C10	C	3.2	1.5	4.7
59	C11	A	480	272	752
60	C12	A	17.5	27.7	45.2
61	C12	B	0.3	0.3	0.6
62	C12	D	69.6	22.7	92.3
63	C12	E	59	55.1	114.1
64	C12	C	73	73.3	146.3
65	C13	C	128.5	111.2	239.8
66	C14	A	34.6	24.4	59
67	C15	C	0	0.1	0.2
68	C16	A	44.2	11.5	55.7
69	C17	C	0	15.4	15.4
70	C19	A	391	399.2	790.2
71	C19	B	814.4	578.4	1,392.70
72	C19	D	394.1	649.5	1,043.60
73	C19	E	102.5	133.3	235.8
74	C19	C	250.9	245.6	496.6
75	C18	A	0.9	0.6	1.5
76	Grand Total		3,281.30	3,132.60	6,413.90
Sheet4

Cell Formulas
Range	Formula
H8:H26	H8	=IF(H7="","",IFERROR(AGGREGATE(14,6,IF(ISNA(MATCH($A$2:$A$75,$G$7:$G7,0)),SUMIFS(INDEX($C$2:$E$75,0,MATCH($H$1,$C$1:$E$1,0)),$A$2:$A$75,$A$2:$A$75,$B$2:$B$75,$H$3),""),ROWS(H$8:H8)),""))
I8:I26	I8	=IF(H8="","",H8/SUM($H$8:$H$26))
H27:I27	H27	=SUM(H8:H26)
G8:G26	G8	=IF(H8="","",INDEX($A$2:$A$75,MATCH(2,1/ISNA(MATCH($A$2:$A$75,$G$7:$G7,0))/(SUMIFS(INDEX($C$2:$E$75,0,MATCH($H$1,$C$1:$E$1,0)),$A$2:$A$75,$A$2:$A$75,$B$2:$B$75,$H$3)=H8))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fluff]

Another option without the helper columns

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Category	Country	Year1	Year2	Total			Cat	Total
2	C01	F	0.5	500	1		F	C19	1952.9
3	C01	F	0	0.2	0.2		Year1	C11	480
4	C01	F	7.6	4.8	12.4			C12	219.4
5	C02	F	0.8	29.4	30.3			C20	216.6
6	C02	F	22.5	35	57.5			C13	128.5
7	C03	F	0.2	79.6	79.8			C09	51.4
8	C04	F	1.1	0.2	1.3			C06	51
9	C05	F	0	0	0			C10	45.3
10	C06	F	51	14.4	65.4			C16	44.2
11	C20	F	28.2	29.8	58			C14	34.6
12	C20	F	1.5	1.9	3.4			C02	23.3
13	C20	F	104.5	145.8	250.3			C07	21.7
14	C20	F	82.4	59.9	142.3			C01	8.1
15	C07	F	0.1	0.1	0.2			C08	1.8
16	C07	F	21.6	16.6	38.2			C04	1.1
17	C08	F	1.8	1.5	3.3			C18	0.9
18	C09	F	51.4	40	91.5			C03	0.2
19	C10	F	42	50.8	92.8			C05	0
20	C10	F	0.1	0.3	0.5			C15	0
21	C10	F	3.2	1.5	4.7			C17	0
22	C11	F	480	272	752
23	C12	F	17.5	27.7	45.2
24	C12	F	0.3	0.3	0.6
25	C12	F	69.6	22.7	92.3
26	C12	F	59	55.1	114.1
27	C12	F	73	73.3	146.3
28	C13	F	128.5	111.2	239.8
29	C14	F	34.6	24.4	59
30	C15	F	0	0.1	0.2
31	C16	F	44.2	11.5	55.7
32	C17	F	0	15.4	15.4
33	C19	F	391	399.2	790.2
34	C19	F	814.4	578.4	1,392.70
35	C19	F	394.1	649.5	1,043.60
36	C19	F	102.5	133.3	235.8
37	C19	F	250.9	245.6	496.6
38	C18	F	0.9	0.6	1.5
39	C01	A	0.5	0.4	1
40	C01	B	0	0.2	0.2
41	C01	C	7.6	4.8	12.4
42	C02	E	0.8	29.4	30.3
43	C02	C	22.5	35	57.5
44	C03	C	0.2	79.6	79.8
45	C04	A	1.1	0.2	1.3
46	C05	C	0	0	0
47	C06	A	51	14.4	65.4
48	C20	A	28.2	29.8	58
49	C20	B	1.5	1.9	3.4
50	C20	D	104.5	145.8	250.3
51	C20	C	82.4	59.9	142.3
52	C07	B	0.1	0.1	0.2
53	C07	C	21.6	16.6	38.2
54	C08	C	1.8	1.5	3.3
55	C09	C	51.4	40	91.5
56	C10	A	42	50.8	92.8
57	C10	B	0.1	0.3	0.5
58	C10	C	3.2	1.5	4.7
59	C11	A	480	272	752
60	C12	A	17.5	27.7	45.2
61	C12	B	0.3	0.3	0.6
62	C12	D	69.6	22.7	92.3
63	C12	E	59	55.1	114.1
64	C12	C	73	73.3	146.3
65	C13	C	128.5	111.2	239.8
66	C14	A	34.6	24.4	59
67	C15	C	0	0.1	0.2
68	C16	A	44.2	11.5	55.7
69	C17	C	0	15.4	15.4
70	C19	A	391	399.2	790.2
71	C19	B	814.4	578.4	1,392.70
72	C19	D	394.1	649.5	1,043.60
73	C19	E	102.5	133.3	235.8
74	C19	C	250.9	245.6	496.6
75	C18	A	0.9	0.6	1.5
76	Grand Total		3,281.30	3,132.60	6,413.90
Data

Cell Formulas
Range	Formula
H2:H21	H2	=SORTBY(UNIQUE(FILTER(A2:A75,B2:B75=G2)),SUMIFS(INDEX(C2:D75,0,MATCH(G3,C1:D1,0)),B2:B75,G2,A2:A75, UNIQUE(FILTER(A2:A75,B2:B75=G2))),-1)
I2:I21	I2	=SUMIFS(INDEX(C2:D75,0,MATCH(G3,C1:D1,0)),A2:A75,H2#,B2:B75,G2)
Dynamic array formulas.

 

[Amenendez]

Many thanks Jason. Much appreciated

 

[Amenendez]

Thanks so much Fluff. I will take your last approach as it sorts out perfectly well my request.

Many thansks

 

[Fluff]

Glad we could help & thanks for the feedback