Sumproduct formula with Right formula

[NatiTavor]

Hi,
I need to sum ammount A and ammount B under "main code" where the connection is thru "shortened code" and "code" (shortened code is the right 5 digits of code)
The explanation is in the file attached.
Columns K and L, are not needed and only example for the result I'm looking for.
Tia
Nati

question to Mrexcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	code	ammount A	ammount B				main code	shortened code	ammount A	ammount B					main code	ammount A	ammount B
2	30026400	1363.91	1186.77				1	26400	5,772.53	6,759.87	11,951.00	21,588.20			1	11,951.00	21,588.20
3	30026400	1274.53	1643.65					22310	6,178.47	14,828.33					2	36,452.27	56,457.25
4	30026400	1392.93	2127				2	23100	10,226.57	27,084.47	36,452.27	56,457.25			3	27,668.29	83,022.93
5	30026400	1741.16	1802.45					50148	26,225.70	29,372.78
6	30023100	1985.07	6200.57				3	2115	11,441.74	46,513.19	27,668.29	83,022.93
7	30023100	1844.19	6664.26					9576	16,226.55	36,509.74
8	30023100	1460.55	1749.7
9	30023100	504.53	333.06
10	30023100	1968.78	6365.24
11	30023100	2463.45	5771.64
12	30002115	1677.7	6040.77
13	30002115	1801.77	6240.66
14	30002115	1750.14	6300.52
15	30002115	1487.62	6720.55
16	30002115	1225.1	4410.36
17	30002115	1750.14	8400.69
18	30002115	1749.27	8399.64
19	30022310	1873.19	4495.66
20	30022310	1680.11	4032.26
21	30022310	1680.11	4032.26
22	30022310	945.06	2268.15
23	30009576	2598.32	5846.22
24	30009576	7210.25	16223.063
25	30009576	356.99	803.2275
26	30009576	4102.87	9231.4575
27	30009576	1958.12	4405.77
28	30050148	8149.762	9127.7334
29	30050148	1365.9812	1529.8989
30	30050148	5082.9484	5692.9022
31	30050148	2791.1872	3126.1297
32	30050148	8835.818	9896.1162
33
גיליון2

Cell Formulas
Range	Formula
I2:J2	I2	=B2+B3+B4+B5
K2:L2,K6:L6,K4:L4	K2	=I2+I3
P2:Q2	P2	=K2
P3:Q3	P3	=K4
P4:Q4	P4	=K6
I3:J3	I3	=SUM(B19:B22)
I4:J4	I4	=SUM(B6:B11)
I5:J5	I5	=SUM(B28:B32)
I6:J6	I6	=SUM(B12:B18)
I7:J7	I7	=SUM(B23:B27)

 

[maabadi]

Welcome to MrExcel Message Board. Try this:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	code	ammount A	ammount B				main code	shortened code	ammount A	ammount B					main code	ammount A	ammount B
2	30026400	1363.91	1186.77				1	26400	5772.53	6759.87	11951	21588.2			1	11951	21588.2
3	30026400	1274.53	1643.65					22310	6178.47	14828.33					2	36452.2668	56457.25042
4	30026400	1392.93	2127				2	23100	10226.57	27084.47	36452.2668	56457.25042			3	27668.29	83022.9275
5	30026400	1741.16	1802.45					50148	26225.6968	29372.78042
6	30023100	1985.07	6200.57				3	2115	11441.74	46513.19	27668.29	83022.9275
7	30023100	1844.19	6664.26					9576	16226.55	36509.7375
8	30023100	1460.55	1749.7
9	30023100	504.53	333.06
10	30023100	1968.78	6365.24
11	30023100	2463.45	5771.64
12	30002115	1677.7	6040.77
13	30002115	1801.77	6240.66
14	30002115	1750.14	6300.52
15	30002115	1487.62	6720.55
16	30002115	1225.1	4410.36
17	30002115	1750.14	8400.69
18	30002115	1749.27	8399.64
19	30022310	1873.19	4495.66
20	30022310	1680.11	4032.26
21	30022310	1680.11	4032.26
22	30022310	945.06	2268.15
23	30009576	2598.32	5846.22
24	30009576	7210.25	16223.0625
25	30009576	356.99	803.2275
26	30009576	4102.87	9231.4575
27	30009576	1958.12	4405.77
28	30050148	8149.762	9127.73344
29	30050148	1365.9812	1529.898944
30	30050148	5082.9484	5692.902208
31	30050148	2791.1872	3126.129664
32	30050148	8835.818	9896.11616
33
Sheet1

Cell Formulas
Range	Formula
I2:I7	I2	=SUMPRODUCT((RIGHT($A$2:$A$32,5)*1=$H2)*($B$2:$B$32))
J2:J7	J2	=SUMPRODUCT((RIGHT($A$2:$A$32,5)*1=$H2)*($C$2:$C$32))
K6:L6,K4:L4,K2:L2	K2	=I2+I3
P2:Q2	P2	=K2
P3:Q3	P3	=K4
P4:Q4	P4	=K6

 

[NatiTavor]

works perfectly
Thx a lot

 

[maabadi]

You're Welcome & Thanks for feedback.