#### singingsister

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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Stormseed

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

##### Well-known Member
Hi,

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

#### DonkeyOte

##### MrExcel MVP
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...

#### singingsister

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

##### Legend
Like this perhaps

Excel Workbook
ABCD
1AreaCustomerW23W24
2GB1Smith456.84
3GB1Jones45447.6545618.70
4GB2Jenkins46583.00
5GB2X778.49789.47
6GB3Y159.4545694.87
7GB4Z64456.77
8GB6A781.78
9
10
11
12GB145904.4945618.7
Sheet1

#### singingsister

##### Board Regular
Like this perhaps

Excel Workbook
ABCD
1AreaCustomerW23W24
2GB1Smith456.84*
3GB1Jones45447.6545618.70
4GB2Jenkins46583.00*
5GB2X778.49789.47
6GB3Y159.4545694.87
7GB4Z64456.77*
8GB6A781.78*
9****
10****
11****
12GB1*45904.4945618.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

##### MrExcel MVP
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

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

##### MrExcel MVP
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.

Replies
14
Views
841
Replies
0
Views
807

1,191,684
Messages
5,987,994
Members
440,124
Latest member
dippy_egg

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