Using SUMPRODUCT to matchi dentifiers in 2 columns and 1 row

[Rainmanne]

I've got data organised in the following way: 2 first columns are identifiers and the rest of the columns are values by year (a year per column). So I need to summarise it in a different table based on 2 identifiers an by year. So each value in the summary table should match the combination of two identifiers in the first two columns and organaised by year. There could be multiple rows matching the combination of the identifiers thus using the SUMPRODUCT formula:

=SUMPRODUCT((--(FS!$B$4:$B$150=CF!$A5)),(--(FS!$C$4:$C$150=CF!$B5)),FS!G$4:G$150)

But ideally I would like to capture the last array with the data based on the year rather hardwire it into the formula as it is now. I tried to use INDEX/MATCH in the last part but it does not work.

Also, SUMPRODUCT seems to be quite heavy on resources so I wonder if it's possible to replace it with something less resource hungry?

 

[Peter_SSs]

Some small sample data and expected results would probably help clarify your requirement.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Rainmanne]

Sorry, it has taken some time to set it up. Please see two sheets below:

test for forum.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	A ID	1 ID	2 ID	3 ID	Rounding	Thousands	Thousands	Thousands	Thousands	Thousands	Thousands
2	0	0	0	0	Statement Date	12/31/2009	12/31/2010	12/31/2011	12/31/2012	12/31/2013	12/31/2014
3	0	0	0	0	Periods	12	12	12	12	12	12
4	0	5	0	0	TOTAL
5	1050	5	5	5	PR1a	2,979,103	4,580,682	6,708,484	7,028,805	8,904,704	11,797,517
6	1100	5	5	5	PR1b	0	0	0	0	0	0
7	1150	5	7	7	PR2a	0	0	0	0	0	0
8	20001	5	7	7	PR2b	134,771	128,959	41,780	34,886	931	0
9	1200	5	10	15	PR3	3,832,272	4,037,236	4,635,904	6,669,683	8,418,574	9,268,139
10	1300	5	11	20	Disc	182,489	192,249	456,584	446,188	325,620	194,104
11	1230	5	13	80	PR5	0	0	0	0	0	0
12	1240	5	13	16	PR6	115,165	272,735	505,120	1,164,202	724,518	418,548
13	1250	5	12	80	PR4a	548,292	822,167	670,009	578,573	788,704	950,439
14	20013	5	12	80	PR4b	416,674	615,492	998,823	1,441,171	1,718,291	2,137,179
15	1670	5	25	65	PR8a	409,753	316,422	440,122	709,920	853,489	1,079,029
16	1680	5	25	65	PR8b	0	0	0	0	0	0
17	1400	5	15	25	PR71	2,521	5,219	7,043	7,593	8,958	17,285
18	1450	5	16	25	PR72	484,043	356,113	452,235	702,019	724,378	688,650
19	1500	5	17	25	PR73a	1,550	9,246	23,732	54,249	68,626	47,987
20	20004	5	17	25	PR73b	102,735	88,191	9,483	36,293	9,983	45,862
21	1550	5	20	35	PR74	38,319	79,302	47,704	92,963	136,456	107,396
22	1600	5	20	35	PR74	144,154	38,107	114,842	301,660	403,798	287,497
23	9950	35	221	135	Exchange Rate	0.57430	0.60017	0.53311	0.48936	0.42335	0.37627
Base

test for forum.xlsx
	A	B	C	D	E	F	G	H	I	J
1	1 ID	2 ID	3 ID			2010	2011	2012	2013	2014
2					SHEET
3				TCA	TOTAL	5,417,652	5,948,240	6,948,274	7,779,407	8,441,915
4	5	5	5	C	PR1	1,787,968	2,442,007	3,282,864	2,975,645	3,350,573
5	5	7	7	MS	PR2	80,886	68,749	20,445	14,769	350
6	5	10	15		PR3	2,300,015	2,152,291	2,268,626	2,823,610	3,167,657
7	5	11	20		Disc	109,524	102,490	223,434	188,894	122,521
8	5			TR	Net PR3	2,190,490	2,049,801	2,045,192	2,634,717	3,045,136
9	5	12	80	OR	PR4	579,144	766,430	816,660	855,059	943,307
10	5	13	80	RCA	PR5	0	0	0	0	0
11	5	13	16	RCA	PR6	69,119	145,398	247,186	492,865	272,614
12	5	15	25	I	PR71	1,513	2,782	3,447	3,214	3,371
13	5	16	25	I	PR72	290,508	189,847	221,306	297,200	272,562
14	5	17	25	I	PR73	62,589	51,945	16,254	38,331	29,578
15	5	20	35	I	PR74	109,515	62,592	79,544	167,064	203,281
16	5				Total RR7	464,125	307,166	320,550	505,809	508,792
17	5	25	65	OCA	PR8	245,921	168,688	215,378	300,545	321,142
18	35	221	135	Exch	Exchange Rate	0.60017	0.53311	0.48936	0.42335	0.37627
Table

Cell Formulas
Range	Formula
F1:J1	F1	=RIGHT(OFFSET(Base!E2,,IF(COUNTA(Base!$2:$2)=8,COUNTA(Base!$2:$2)-7,IF(COUNTA(Base!$2:$2)=9,COUNTA(Base!$2:$2)-8,COUNTA(Base!$2:$2)-9))),4)
F3:J3	F3	=SUM(F4:F5,F8,F9:F15,F17:F17)
F4:J7,F9:J15,F17:J17	F4	=SUMPRODUCT((--(Base!$B$2:$B$23=$A4)),(--(Base!$C$2:$C$23=$B4)),(--(Base!$D$2:$D$23=$C4)),Base!F$2:F$23)*F$18
F8:J8	F8	=F6-F7
F16:J16	F16	=SUM(F12:F15)
F18:J18	F18	=IF(INDEX(Base!$1:$1048576,MATCH($E18,Base!$E:$E,0),MATCH(TRUE,INDEX(RIGHT(Base!$2:$2,4)=F$1,0),0))=0,1,INDEX(Base!$1:$1048576,MATCH($E18,Base!$E:$E,0),MATCH(TRUE,INDEX(RIGHT(Base!$2:$2,4)=F$1,0),0)))

Hopefully it works.

 

[Rainmanne]

Basically I need to match the year column and I cannot figure out how.

 

[Peter_SSs]

I may have more questions to follow, but to try to get some understanding, can you please answer these?

1. Can you confirm whether the dates in row 2 of 'Base' are actual dates (numbers) or text values?
2. Can you explain in words what the OFFSET formula in row 1 of 'Table' is supposed to do?
3. Is 'Table' showing the results that you want? That is what I asked for but I'm not sure that is what you have given since all those cells contain formulas, not manually entered expected results.

 

[Rainmanne]

I may have more questions to follow, but to try to get some understanding, can you please answer these?

1. Can you confirm whether the dates in row 2 of 'Base' are actual dates (numbers) or text values?
2. Can you explain in words what the OFFSET formula in row 1 of 'Table' is supposed to do?
3. Is 'Table' showing the results that you want? That is what I asked for but I'm not sure that is what you have given since all those cells contain formulas, not manually entered expected results.

Thanks a lot for looking into it! Please see below:

1. The dates are not actual dates but numbers. If you look into Format Cells... they are formatted as numbers.
2. It takes the year from the table for each columns. Basically the year is the four last digits of the date in row 2 in Base sheet.
3. No, the results are wrong. I need to take the data from the last five years. So the years in Table are correct and the exchange rates in row 18 are correct as well. But the other data are taken from a wrong year. The current formula only works if there are 5 years or less in the Base. It doesn't if the number of years is higher. Probably there's a more elegant way to do it.

Thanks a lot again!

 

[Peter_SSs]

. The dates are not actual dates but numbers. If you look into Format Cells... they are formatted as numbers.

That is still somewhat unclear to me. In a blank cell in sheet 'Base' put this formula and tell us the result =ISNUMBER(H2)

It takes the year from the table for each columns. Basically the year is the four last digits of the date in row 2 in Base sheet.

That is not clear to me either. The last four digits in F2 of 'Base' are 2009 but F1 in 'Table' says 2010

3. No, the results are wrong.

So to better understand, I would like to see the correct results with a clear explanation for a couple of them how you came up with those results manually.

 

[Rainmanne]

That is still somewhat unclear to me. In a blank cell in sheet 'Base' put this formula and tell us the result =ISNUMBER(H2)

It returns FALSE for H2

That is not clear to me either. The last four digits in F2 of 'Base' are 2009 but F1 in 'Table' says 2010

Yes, because the Row1 in Table is correct. The formula takes the last 5 years regardless how many years are in Base. The issue I have I cannot do the same for the data, so I need to match the year in Row1 with the data under the same year in Base. At the moment the data in the formula in the Table columns is hardwired to the columns in the base. It works fine for the cases where there 5 years or less of data. But if it's more than 5 years of data, the columns are mismatched. The exchange rate row in Table is also correct as it uses a different formula.

So to better understand, I would like to see the correct results with a clear explanation for a couple of them how you came up with those results manually.

Please see below the expected results. I have hardwired 2010 column in the table to G column in Base and filled it right.

test for forum correct.xlsx
	A	B	C	D	E	F	G	H	I	J
1	1 ID	2 ID	3 ID			2010	2011	2012	2013	2014
2					SHEET
3				TCA	TOTAL	6,696,470	7,569,467	8,992,396	9,498,192	########
4	5	5	5	C	PR1	2,749,188	3,576,360	3,439,616	3,769,806	4,439,052
5	5	7	7	MS	PR2	77,397	22,273	17,072	394	0
6	5	10	15		PR3	2,423,028	2,471,447	3,263,876	3,564,003	3,487,323
7	5	11	20		Disc	115,382	243,409	218,347	137,851	73,036
8	5			TR	Net PR3	2,307,646	2,228,037	3,045,530	3,426,152	3,414,287
9	5	12	80	OR	PR4	862,840	889,671	988,382	1,061,336	1,161,778
10	5	13	80	RCA	PR5	0	0	0	0	0
11	5	13	16	RCA	PR6	163,687	269,285	569,714	306,725	157,487
12	5	15	25	I	PR71	3,132	3,755	3,716	3,792	6,504
13	5	16	25	I	PR72	213,728	241,091	343,540	306,665	259,118
14	5	17	25	I	PR73	58,479	17,707	44,308	33,279	35,313
15	5	20	35	I	PR74	70,465	86,655	193,113	228,717	148,586
16	5				Total RR7	345,805	349,208	584,676	572,453	449,521
17	5	25	65	OCA	PR8	189,907	234,633	347,406	361,325	406,006
18	35	221	135	Exch	Exchange Rate	0.60017	0.53311	0.48936	0.42335	0.37627
Table

Cell Formulas
Range	Formula
F1:J1	F1	=RIGHT(OFFSET(Base!E2,,IF(COUNTA(Base!$2:$2)=8,COUNTA(Base!$2:$2)-7,IF(COUNTA(Base!$2:$2)=9,COUNTA(Base!$2:$2)-8,COUNTA(Base!$2:$2)-9))),4)
F3:J3	F3	=SUM(F4:F5,F8,F9:F15,F17:F17)
F4:J7,F9:J15,F17:J17	F4	=SUMPRODUCT((--(Base!$B$2:$B$23=$A4)),(--(Base!$C$2:$C$23=$B4)),(--(Base!$D$2:$D$23=$C4)),Base!G$2:G$23)*F$18
F8:J8	F8	=F6-F7
F16:J16	F16	=SUM(F12:F15)
F18:J18	F18	=IF(INDEX(Base!$1:$1048576,MATCH($E18,Base!$E:$E,0),MATCH(TRUE,INDEX(RIGHT(Base!$2:$2,4)=F$1,0),0))=0,1,INDEX(Base!$1:$1048576,MATCH($E18,Base!$E:$E,0),MATCH(TRUE,INDEX(RIGHT(Base!$2:$2,4)=F$1,0),0)))

 

[Peter_SSs]

OK, thanks. I think that I sort of understand now.

It returns FALSE for H2

OK, so those dates must be text values then as far as I can see. Note that cell formatting does not alter the underlying cell value so a cell can be formatted as number but contain text.

You may still have to adjust some ranges to suit your possible data but I would try to avoid the huge ones (eg Base!$1:$1048576). Just make them big enough to be sure to cover any expected data.
I'm also not too sure about how you want to handle it if there are less than 5 years in 'Base'.
Anyway, this may give you something to build on. The formulas that I have adjusted are green and I have not altered the white cell formulas.

Rainmanne.xlsm
	A	B	C	D	E	F	G	H	I	J
1	1 ID	2 ID	3 ID			2010	2011	2012	2013	2014
2					SHEET
3				TCA	TOTAL	6,696,470	7,569,467	8,992,396	9,498,192	10,028,131
4	5	5	5	C	PR1	2,749,188	3,576,360	3,439,616	3,769,806	4,439,052
5	5	7	7	MS	PR2	77,397	22,273	17,072	394	0
6	5	10	15		PR3	2,423,028	2,471,447	3,263,876	3,564,003	3,487,323
7	5	11	20		Disc	115,382	243,409	218,347	137,851	73,036
8	5			TR	Net PR3	2,307,646	2,228,037	3,045,530	3,426,152	3,414,287
9	5	12	80	OR	PR4	862,840	889,671	988,382	1,061,336	1,161,778
10	5	13	80	RCA	PR5	0	0	0	0	0
11	5	13	16	RCA	PR6	163,687	269,285	569,714	306,725	157,487
12	5	15	25	I	PR71	3,132	3,755	3,716	3,792	6,504
13	5	16	25	I	PR72	213,728	241,091	343,540	306,665	259,118
14	5	17	25	I	PR73	58,479	17,707	44,308	33,279	35,313
15	5	20	35	I	PR74	70,465	86,655	193,113	228,717	148,586
16	5				Total RR7	345,805	349,208	584,676	572,453	449,521
17	5	25	65	OCA	PR8	189,907	234,633	347,406	361,325	406,006
18	35	221	135	Exch	Exchange Rate	0.60017	0.53311	0.48936	0.42335	0.37627
Table

Cell Formulas
Range	Formula
F1	F1	=RIGHT(LOOKUP("z",Base!$F2:$Z2),4)-4
G1:J1	G1	=F1+1
F3:J3	F3	=SUM(F4:F5,F8,F9:F15,F17:F17)
F4:J7,F9:J15,F17:J17	F4	=SUMPRODUCT((--(Base!$B$5:$B$40=$A4)),(--(Base!$C$5:$C$40=$B4)),(--(Base!$D$5:$D$40=$C4)),INDEX(Base!$F$5:$Z$40,0,MATCH("*/"&F$1,Base!$F$2:$Z$2,0)))*F$18
F8:J8	F8	=F6-F7
F16:J16	F16	=SUM(F12:F15)
F18:J18	F18	=IF(INDEX(Base!$F5:$Z40,MATCH($E18,Base!$E5:$E40,0),MATCH("*"&Table!F1,Base!$F2:$Z2,0))=0,1,INDEX(Base!$F5:$Z40,MATCH($E18,Base!$E5:$E40,0),MATCH("*"&Table!F1,Base!$F2:$Z2,0)))

 

[Rainmanne]

OK, thanks. I think that I sort of understand now.


OK, so those dates must be text values then as far as I can see. Note that cell formatting does not alter the underlying cell value so a cell can be formatted as number but contain text.

You may still have to adjust some ranges to suit your possible data but I would try to avoid the huge ones (eg Base!$1:$1048576). Just make them big enough to be sure to cover any expected data.
I'm also not too sure about how you want to handle it if there are less than 5 years in 'Base'.
Anyway, this may give you something to build on. The formulas that I have adjusted are green and I have not altered the white cell formulas.

Rainmanne.xlsm
	A	B	C	D	E	F	G	H	I	J
1	1 ID	2 ID	3 ID			2010	2011	2012	2013	2014
2					SHEET
3				TCA	TOTAL	6,696,470	7,569,467	8,992,396	9,498,192	10,028,131
4	5	5	5	C	PR1	2,749,188	3,576,360	3,439,616	3,769,806	4,439,052
5	5	7	7	MS	PR2	77,397	22,273	17,072	394	0
6	5	10	15		PR3	2,423,028	2,471,447	3,263,876	3,564,003	3,487,323
7	5	11	20		Disc	115,382	243,409	218,347	137,851	73,036
8	5			TR	Net PR3	2,307,646	2,228,037	3,045,530	3,426,152	3,414,287
9	5	12	80	OR	PR4	862,840	889,671	988,382	1,061,336	1,161,778
10	5	13	80	RCA	PR5	0	0	0	0	0
11	5	13	16	RCA	PR6	163,687	269,285	569,714	306,725	157,487
12	5	15	25	I	PR71	3,132	3,755	3,716	3,792	6,504
13	5	16	25	I	PR72	213,728	241,091	343,540	306,665	259,118
14	5	17	25	I	PR73	58,479	17,707	44,308	33,279	35,313
15	5	20	35	I	PR74	70,465	86,655	193,113	228,717	148,586
16	5				Total RR7	345,805	349,208	584,676	572,453	449,521
17	5	25	65	OCA	PR8	189,907	234,633	347,406	361,325	406,006
18	35	221	135	Exch	Exchange Rate	0.60017	0.53311	0.48936	0.42335	0.37627
Table

Cell Formulas
Range	Formula
F1	F1	=RIGHT(LOOKUP("z",Base!$F2:$Z2),4)-4
G1:J1	G1	=F1+1
F3:J3	F3	=SUM(F4:F5,F8,F9:F15,F17:F17)
F4:J7,F9:J15,F17:J17	F4	=SUMPRODUCT((--(Base!$B$5:$B$40=$A4)),(--(Base!$C$5:$C$40=$B4)),(--(Base!$D$5:$D$40=$C4)),INDEX(Base!$F$5:$Z$40,0,MATCH("*/"&F$1,Base!$F$2:$Z$2,0)))*F$18
F8:J8	F8	=F6-F7
F16:J16	F16	=SUM(F12:F15)
F18:J18	F18	=IF(INDEX(Base!$F5:$Z40,MATCH($E18,Base!$E5:$E40,0),MATCH("*"&Table!F1,Base!$F2:$Z2,0))=0,1,INDEX(Base!$F5:$Z40,MATCH($E18,Base!$E5:$E40,0),MATCH("*"&Table!F1,Base!$F2:$Z2,0)))

Hi, it's worked perfectly! Thanks so much for your help!