Add up cells

[singingsister]

I have a spreadsheet which lists order placed by customers in a month. Every customer falls under an area code depending on where they are in the country i.e GB1, GB2, GB3 etc. At then end of every month, I have to produce a report showing all orders placed by the area codes.

At the moment, I have all the GB1 customers together, followed by the GB2 customers etc vertically, so at the bottom I can total the orders by simply using SUM. This means every time I want to add a "new" customer to the spreadsheet, I have to insert cells within the spreadsheet. This is time consuming as I have 5 different tabs for the 5 different factories!

What I want to do is not to have to worry about what order the customers are in the list (so I could maybe alphabetise them), but that for the totals at the bottom, Excel looks through the sheet and adds up all the values for the weeks for customers GB1 together, GB2 together etc.

The spreadsheet looks a bit like this:-
Area Customer Week 23 Week 24 Week 25 Week 26
GB1 P Smith $456.84
GB1 L Jones $45447.65 $45618.70
GB2 R Jenkins $46583.00
GB2 XYZ $778.49 $789.47
GB3 XYZ £159.45 $45694.87
GB4 XYZ $64456.77
GB6 XYZ $781.78

Can anyone help?

 

[Stormseed]

Hi

I understand that you want the sum total of all the values of all customers together for all the weeks in all the sheets for each country individually ?

 

[al_b_cnu]

Hi,

assuming the list is sorted by area, have you tried using Subtotal?

 

[DonkeyOte]

have a look at the SUMIF function in Excel

=SUMIF(range containing criteria values, criteria value, range containing totals to sum)

where in your case criteria value = "GB1" etc...

Alertnatively, think about generating a Pivot Table off your data.

 

[singingsister]

Stormseed - yes - every sheet is seperate

al_b_cnu - The list IS sorted by Area - I don't want it to be anymore! I want it alphabetical for ease of everyday use!

lasw10 - I'll have a look at sumif and see if I can make it work for me.

 

[VoG]

Like this perhaps

Excel Workbook
	A	B	C	D
1	Area	Customer	W23	W24
2	GB1	Smith	456.84
3	GB1	Jones	45447.65	45618.70
4	GB2	Jenkins	46583.00
5	GB2	X	778.49	789.47
6	GB3	Y	159.45	45694.87
7	GB4	Z	64456.77
8	GB6	A	781.78
9
10
11
12	GB1		45904.49	45618.7
Sheet1

 

[singingsister]

Like this perhaps

Excel Workbook
	A	B	C	D
1	Area	Customer	W23	W24
2	GB1	Smith	456.84	*
3	GB1	Jones	45447.65	45618.70
4	GB2	Jenkins	46583.00	*
5	GB2	X	778.49	789.47
6	GB3	Y	159.45	45694.87
7	GB4	Z	64456.77	*
8	GB6	A	781.78	*
9	*	*	*	*
10	*	*	*	*
11	*	*	*	*
12	GB1	*	45904.49	45618.7
Sheet1

Kind of - only the totals are at the bottom and add up all the weeks together so all I have is
GB1 = $xxx
GB2 = $xxx
etc

So in your example, row 12 would be GB1 total for all weeks, row 13 would be GB2 total for all weeks etc and then under that a grand total adding up all the GB's for the month!

 

[DonkeyOte]

in which case you could switch to SUMPRODUCT

=SUMPRODUCT(--($A$1:$A$8="GB1"),--($C$1:$Z$8))

where A1:A8 contains criteria and C1:Z8 = weeks

adjust ranges to fit your data... and obviously ="GB1" could be =cellref where cell contains "GB1"

 

[singingsister]

in which case you could switch to SUMPRODUCT

=SUMPRODUCT(--($A$1:$A$8="GB1"),--($C$1:$Z$8))

where A1:A8 contains criteria and C1:Z8 = weeks

adjust ranges to fit your data... and obviously ="GB1" could be =cellref where cell contains "GB1"

I've tried it but can't seem to make it work. I've downloaded the HTML maker thing to my PC so I could show you the sheet but I'm not sure how to use it!

 

[DonkeyOte]

can you provide us with some ranges for one given sheet ?

data range containing GB1 etc...
data range containing week values

cell in which GB1 is listed for your totals
cell in which you want total of GB1 to be returned.

we can then revise the formula for your exact setup which may help.