# Sum

#### brisbane bob

##### Board Regular
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 ?

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Smitty

##### Legend
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

##### Board Regular
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

##### Board Regular
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

##### Board Regular
Hi Dylan

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

Much appreciated

##### MrExcel MVP
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
ABCDEFGHIJKLM
1DescriptionSiteCodeValueSite
2Bank Chargessite A2\$85.00Descriptionsite Asite Bsite Csite Dsite E
3Bank Chargessite A7\$98.00Bank Charges183579781750
4Vehicle costssite A10\$14.00Vehicle costs1435414980
5Rentsite A12\$15.00Rent158501225
6Wagessite A15\$11.00Wages231061435457
7Wagessite A16\$12.00Electricity038585100
8Bank Chargessite B2\$57.00Stationery01412500
9Electricitysite B4\$3.00Salaries005789781
10Rentsite B5\$85.00
11Stationerysite B7\$14.00
12Vehicle costssite B10\$354.00Sum of ValueSite
13Wagessite B15\$81.00Descriptionsite Asite Bsite Csite Dsite EGrand Total
14Wagessite B16\$25.00Bank Charges1835797817501285
15Electricitysite C3\$85.00Electricity38585100273
16Salariessite C6\$57.00Rent15851225137
17Bank Chargessite C7\$897.00Salaries57897811035
18Vehicle costssite C11\$14.00Stationery14125139
19Bank Chargessite C13\$81.00Vehicle costs143541498480
20Stationerysite C14\$25.00Wages231061435457554
21Wagessite C15\$14.00Grand Total235619127314633133903
22Stationerysite C26\$15.00
23Stationerysite C29\$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

##### Board Regular

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

Rgds

#### brisbane bob

##### Board Regular
Thanks for that - it sort of makes sense.

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

#### galileogali

##### Well-known Member
COERSION??What is this, Bob?

GALILEOGALI

Replies
1
Views
433
Replies
16
Views
1K
Replies
6
Views
175
Replies
2
Views
137
Replies
5
Views
219

1,191,558
Messages
5,987,274
Members
440,087
Latest member
Ruppert23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back