Sum

[brisbane bob]

I have approximately 500 codes representing about 300 descriptions (i.e. some descriptions have more than one code) across 8 sites. The data is in four columns about 2,500 lines deep. How can I sum the data by Description by Site. I think it must revolve around array formulas but I'm new at all of this. I would be grateful for any help.

Any ideas?

Description, Site, Code, Value
Bank Charges, site A, 2, $85.00
Bank Charges, site A, 7, $98.00
Vehicle costs, site A, 10, $14.00
Rent, site A, 12, $15.00
Wages, site A, 15, $11.00
Wages, site A 16 $12.00
Bank Charges, site B 2 $57.00
Electricity, site B 4 $3.00
Rent, site B 5 $85.00
Stationery, site B 7 $14.00
Vehicle costs site B 10 $354.00
Wages site B 15 $81.00
Wages site B 16 $25.00
Electricity site C 3 $85.00
Salaries site C 6 $57.00
Bank Charges site C 7 $897.00
Vehicle costs site C 11 $14.00
Bank Charges site C 13 $81.00
Stationery site C 14 $25.00
Wages site C 15 $14.00
Stationery site C 26 $15.00
Stationery site C 29 $85.00
Bank Charges site D 1 $14.00
Bank Charges site D 2 $3.00
Vehicle costs site D 10 $98.00
Rent site D 12 $12.00
Wages site D 15 $354.00
Salaries site D 19 $897.00
Electricity site D 34 $85.00
Bank Charges site E 7 $50.00
Rent site E 12 $25.00
Wages site E 15 $57.00
Salaries site E 21 $81.00
Electricity site E 34 $100.00

$3,913.00


Bank charges Site A ?
Bank Charges Site C ?

 

[galileogali]

Another otion: Pivot tables, with fields Hidden - Visible

Pivot table.xls
	A	B	C	D	E	F	G	H	I	J
1	Description	Site	Code	Value
2	BankCharges	siteA	2	$85.00		SumadeValue	Site
3	BankCharges	siteA	7	$98.00		Description	siteA	siteB	siteC	Totalgeneral
4	Vehiclecosts	siteA	10	$14.00		BankCharges	183	57	978	1218
5	Rent	siteA	12	$15.00		Electricity		3	85	88
6	Wages	siteA	15	$11.00		Rent	15	85		100
7	Wages	siteA	16	$12.00		Salaries			57	57
8	BankCharges	siteB	2	$57.00		Stationery		14	125	139
9	Electricity	siteB	4	$3.00		Vehiclecosts	14	354	14	382
10	Rent	siteB	5	$85.00		Wages	23	106	14	143
11	Stationery	siteB	7	$14.00		Totalgeneral	235	619	1273	2127
12	Vehiclecosts	siteB	10	$354.00
13	Wages	siteB	15	$81.00
14	Wages	siteB	16	$25.00
15	Electricity	siteC	3	$85.00
16	Salaries	siteC	6	$57.00
17	BankCharges	siteC	7	$897.00
18	Vehiclecosts	siteC	11	$14.00
19	BankCharges	siteC	13	$81.00
20	Stationery	siteC	14	$25.00
21	Wages	siteC	15	$14.00
22	Stationery	siteC	26	$15.00
23	Stationery	siteC	29	$85.00
Hoja1

GALILEOGALI

 

[brisbane bob]

See links above

 

[brisbane bob]

Is it possible to use INDIRECT with the array or coersion forumula? I'm probably just making a mistake although I'm normally OK with INDIRECT

 

[Aladin Akyurek]

Is it possible to use INDIRECT with the array or coersion forumula? I'm probably just making a mistake although I'm normally OK with INDIRECT

What did you try?

 

[brisbane bob]

Hi

I used the forumula below which you wrote and it works great, retriving data from a workbook called TB feeder.xls from a sheet called Jul 06 Split. I'm trying to make the "Jul 06" part the INDIRECT part but Excel won't even accept the formula I'm trying. I've tried putting the INDIRECT in just about every position but no joy.

=SUMPRODUCT((('[TB feeder.xls]Jul 06 split'!$B$14:$B$637)=$E147)*(('[TB feeder.xls]Jul 06 split'!$D$14:$D$637)=$D147)*('[TB feeder.xls]Jul 06 split'!$K$14:$K$637))/1000


Rgds

 

[Aladin Akyurek]

Hi

I used the forumula below which you wrote and it works great, retriving data from a workbook called TB feeder.xls from a sheet called Jul 06 Split. I'm trying to make the "Jul 06" part the INDIRECT part but Excel won't even accept the formula I'm trying. I've tried putting the INDIRECT in just about every position but no joy.

=SUMPRODUCT((('[TB feeder.xls]Jul 06 split'!$B$14:$B$637)=$E147)*(('[TB feeder.xls]Jul 06 split'!$D$14:$D$637)=$D147)*('[TB feeder.xls]Jul 06 split'!$K$14:$K$637))/1000


Rgds

Why did you replace the comma syntax, which is a bit faster...

A couple of things you need to take into account:

1) If you have to use INDIRECT, the target books must be open. This can be resolved using INDIRECT.EXT of the morefunc.xll add-in).

2) INDIRECT/INDIRECT.EXT will slow down your workbook.

That said...

=SUMPRODUCT(
   --(INDIRECT.EXT("'[TB feeder.xls]"&$A$2&" split'!B14:B637"))=$E147)
   --(INDIRECT.EXT("'[TB feeder.xls]"&$A$2&" split'!D14:D637"))=$D147)
   INDIRECT.EXT("'[TB feeder.xls]"&$A$2&" split'!K14:K637"))/1000

 

[brisbane bob]

Aladin

Thanks for your reply - your help is really appreciated.

I didn't realise that the target had to be open for INDIRECT to work (!) but the INDIRECT.EXT (which I'd never even heard of) works a treat.

Again, thanks