Sumproduct or Match(?) problem

[konew1]

I have about 10 sets of data each headed 1, 2, or 3 (only 2 sets in example) and lots of lines each with a code in column B. The codes repeat several times

I need a summary panel so that each of the column B codes shows a single result for the total of all the rows with a particular code (eg BANK) with separte results for the columns headed 1,2 and 3.
I have tried sumproduct and sumif with no luck. I think it needs a CSE but need help there.

Book31.xls
	A	B	C	D	E	F	G	H	I
23			1	2	3		1	2	3
24	IssuedCapital	CAP	(1,000)	(1,000)	(1,000)		(23,331)	(23,331)	(23,331)
25	RetainedEarningsB/Fwd	RE	-	-	(8,788)		-	(30,227)	(37,303)
26	Hedgingreserve(E)	HDG
27	DividendPaid	DIVD					-	-	-
28	Bank-MaintenanceFund(HHV)	BANK	-	37,528	41,951
29	Bank-TillClearinga/c	BANK	3,336	660	598
30	Bank-APClearinga/c	BANK	-	-	-
31	EFTPOSClearinga/c	BANK	-	-	-
32	NationalBank-00a/c	BANK	60,142	52,886	35,917		598,431	11,975	16,716
33	NationalBank-02a/c	BANK	1,208	3,573	1,742
34	Bank-MaintenanceFund(LPV)	BANK
35
36	SUMMARY
37			1	2	3
38		BANK	663,117	106,622	96,925
39		CAP	-24,331	-24,331	-24,331
40		HDG
Sheet1

 

[dafan]

This goes in C38, and you can figure out the rest hopefully

=SUMIF($B$28:$B$34,B$38,C$24:C$34)

(Drag down and right)

 

[PaddyD]

1) if possible, reorganise your data into a form better suited to excel & just use the standard functions

2) if not, formulas of the form:

=SUM(IF(C23:C31="BANK",IF(MOD(COLUMN(D22:N31),4)=0,D23:N31)))
...entered with control + shift + enter, not just enter. alter the =0 check on the mod() for other columns.

 

[konew1]

I think you had a typo, so I changed it to =SUMIF($B$24:$B$34,B$38,C$24:C$34)
That only picks up column C data. I also need column G, and K and every 4th column after that up to column AW. The formula in D38 needs to add column D, H , L etc

 

[dafan]

Wow then I have to agree with PaddyD, thats a weird lay-out.

But this will work too:

=SUMPRODUCT((B24:B34=B38)*(C23:AW23=1)*(C24:AW38))

 

[konew1]

Thanks Paddy. Problem solved