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 ?

 

[Smitty]

Welcome to the Board!

Is your data in a single column or actually in single cells per row?

You can post a representative shot if you follow the link beneath my sig, and/or take a look at SUMPRODUCT in the helpfile.

Hope that helps,

Smitty

 

[brisbane bob]

Thanks

The data is in four columns, each in its own cell under Description, Code, Site and Value

I'm trying to install the spreadsheet cut and paste as you suggested but I'm battling.

 

[Dylan]

Let say your data in column A to D.
In column E2 ="Bank charges site A"
In column F2 =SUMPRODUCT(((A2:A35)="Bank Charges")*((B2:B35)="site A")*(D2:D35))
In column E3 ="Bank charges site C"
In column F3 =SUMPRODUCT(((A2:A35)="Bank Charges")*((B2:B35)="site C")*(D2:D35))

 

[brisbane bob]

Hi Dylan

Thanks a lot - if I enter them as an array formula they work perfectly.

Much appreciated

 

[Aladin Akyurek]

Hi Dylan

Thanks a lot - if I enter them as an array formula they work perfectly.

Much appreciated

Such formulas do not need control+shift+enter...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Description	Site	Code	Value			Site
2	Bank Charges	site A	2	$85.00		Description	site A	site B	site C	site D	site E
3	Bank Charges	site A	7	$98.00		Bank Charges	183	57	978	17	50
4	Vehicle costs	site A	10	$14.00		Vehicle costs	14	354	14	98	0
5	Rent	site A	12	$15.00		Rent	15	85	0	12	25
6	Wages	site A	15	$11.00		Wages	23	106	14	354	57
7	Wages	site A	16	$12.00		Electricity	0	3	85	85	100
8	Bank Charges	site B	2	$57.00		Stationery	0	14	125	0	0
9	Electricity	site B	4	$3.00		Salaries	0	0	57	897	81
10	Rent	site B	5	$85.00
11	Stationery	site B	7	$14.00
12	Vehicle costs	site B	10	$354.00		Sum of Value	Site
13	Wages	site B	15	$81.00		Description	site A	site B	site C	site D	site E	Grand Total
14	Wages	site B	16	$25.00		Bank Charges	183	57	978	17	50	1285
15	Electricity	site C	3	$85.00		Electricity		3	85	85	100	273
16	Salaries	site C	6	$57.00		Rent	15	85		12	25	137
17	Bank Charges	site C	7	$897.00		Salaries			57	897	81	1035
18	Vehicle costs	site C	11	$14.00		Stationery		14	125			139
19	Bank Charges	site C	13	$81.00		Vehicle costs	14	354	14	98		480
20	Stationery	site C	14	$25.00		Wages	23	106	14	354	57	554
21	Wages	site C	15	$14.00		Grand Total	235	619	1273	1463	313	3903
22	Stationery	site C	26	$15.00
23	Stationery	site C	29	$85.00
Sheet1

G3, copied across and down:

=SUMPRODUCT(--($A$2:$A$35=$F3),--($B$2:$B$35=G$2),$D$2:$D$35)

You can also construct a pivot table from your data as shown in F12:L21.

 

[brisbane bob]

Thanks, Aladin.

Can you explain to me what the "--" is and how it works?

Rgds

 

[Aladin Akyurek]

Thanks, Aladin.

Can you explain to me what the "--" is and how it works?

Rgds

Here some links:

http://www.mrexcel.com/board2/viewtopic.php?t=133602
http://tinyurl.com/2pr7ad
http://www.mrexcel.com/board2/viewtopic.php?t=59063

 

[brisbane bob]

Thanks for that - it sort of makes sense.

Can you use COERSION and ARRAY formula reading out of another spreadsheet?

 

[galileogali]

COERSION??What is this, Bob?

GALILEOGALI