Fancy sumproduct / index lookup

[ryansm05]

Hi,

Apologies for not uploading a workbook - company restrictions prevent me from this

However, I need to consolidate the employee data in table 2 at the very bottom and present it as per table 1 below this message. the main issue I'm encountering is that the pay category runs across the row but is then consolidate in the column. If anyone has a solution for this I would be very grateful.

Please use whatever col/row combination that works for you - I can adapt accordingly.

Thanks in advance!
Ryan

Pay Category	19/20	19/20	19/20
P01	P02	P03
Basic Pay	3600	3900	4200
Pension	360	390	420
National Insurance	496.8	538.2	579.6

	19/20	19/20	19/20	19/20	19/20	19/20	19/20	19/20	19/20
	P01	P01	P01	P02	P02	P02	P03	P03	P03
	Basic Pay	Pension	National Insurance	Basic Pay	Pension	National Insurance	Basic Pay	Pension	National Insurance
employee1	1000​	100​	138​	1100​	110​	151.8​	1200​	120​	165.6​
employee2	1200​	120​	165.6​	1300​	130​	179.4​	1400​	140​	193.2​
employee3	1400​	140​	193.2​	1500​	150​	207​	1600​	160​	220.8​

 

[J.Ty.]

Here is my solution. It does not use any sophisticated tools. I simply merged cells into groups of 3, so that the consecutive sums take every third column into account.
If you copy it to the right including formatting, it will act as desired.

J.Ty.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	employee1	1000	100	138	1100	110	151.8	1200	120	165.6		3600			3900			4200
2	employee2	1200	120	165.6	1300	130	179.4	1400	140	193.2		360			390			420
3	employee3	1400	140	193.2	1500	150	207	1600	160	220.8		496.8			538.2			579.6
Sheet1

Cell Formulas
Range	Formula
L1, O1, R1	L1	=SUM(B1:B3)
L2, O2, R2	L2	=SUM(C1:C3)
L3, O3, R3	L3	=SUM(D1:D3)

 

[pgc01]

Hi Ryan
Assuming the data you posted starts in A1,

In B3:

=SUMPRODUCT(($B$9:$J$9=B$2)*($B$10:$J$10=$A3)*$B$11:$J$13)

Copy down and across

 

[ryansm05]

Hi Ryan
Assuming the data you posted starts in A1,

In B3:

=SUMPRODUCT(($B$9:$J$9=B$2)*($B$10:$J$10=$A3)*$B$11:$J$13)

Copy down and across

I was so close but was bracketing the

$B$11:$J$13. I'll remember that for future!​

Thanks for your help - it's much appreciated!

Ryan

 

[pgc01]

I'm glad it helped. Thanks for the feedback.