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?

 

[singingsister]

Ranges are as follows:-

Area - Column A rows 2 - 101 (gb1, gb2 etc)
Customer Name - Column B rows 2 - 101
Weeks Columns C-D-E-F-G (5 weeks in July) also rows 2 - 101

If an order by a customer is placed in a certain week, I find the customer row, move along to the week and put the value of the order in.

The totals at the bottom total up GB1, GB2 etc regardless of week order is placed in.

 

[DonkeyOte]

assuming A105 = "GB1" then to get total in B105

=SUM(IF(($A$2:$A$101=$A105),($C$2:$G$101)))

should be entered as an array using CTRL + SHIFT + ENTER

 

[singingsister]

I don't understand the bit about the array.

I've just copied the formula but I'm getting #value!

 

[DonkeyOte]

retype the formula into the cell and then instead of hitting enter or moving to another cell hold SHIFT + CTRL + ENTER, the formula should become encased in curly brackets, eg

{=...}

I'd still say you're better running a SUMIF under each week, eg

C105 = SUMIF($A$2:$A$101,$A105,C$2:C$101)

and copying across all weeks and down for all rows (105 down where A105 = GB1, A106 = GB2 etc...)

then in B105 = SUM(C105:G105)

and so on for other rows

 

[singingsister]

retype the formula into the cell and then instead of hitting enter or moving to another cell hold SHIFT + CTRL + ENTER, the formula should become encased in curly brackets, eg

{=...}

I'd still say you're better running a SUMIF under each week, eg

C105 = SUMIF($A$2:$A$101,$A105,C$2:C$101)

and copying across all weeks and down for all rows (105 down where A105 = GB1, A106 = GB2 etc...)

then in B105 = SUM(C105:G105)

and so on for other rows

I do like the SUMIF but it's no good for what I want. I can't run it under each week as I'll end up with figures everywhere. The only total under each week I possibly want is the total for the week including all areas. I can't think how to do it to sumif each week. I'd have to have a cell for each GB for each week...that's a lof of figures!

Wish I could get the array thing to work, no matter what I do, I either get =value! or just a 0 no matter how many figures are in the GB1 cells!

 

[Stormseed]

hi Singing girl

would you be able to let us know if the sheets are according to the country names, alike, GB1, GB2, GB3, etc., or are they based on weeks, alike Week1 as a seperate sheet, week 2 as a seperate sheet, etc ? I am sorry but still I was unable to understand what is it that you exactly want ?

Here...once again, I understand, that you want:

--> the sum total of all the values for all the customers across all the weeks pertaining to a particular country and most importantly you have seperate worksheets with the same format but different values, am I correct ?

Your issue seems to be a bit trivial and it can be done very easily unless you get it right the logic to implement it.

 

[singingsister]

LASW10 - can you help please....as you seem to understand what I'm on about

 

[Stormseed]

sorry, i tried to help.

 

[DonkeyOte]

set A105 = GB1

copy the below into B105

=SUM(IF(($A$2:$A$101=$A105),($C$2:$G$101)))

You should get #VALUE error

Activate the formula bar by clicking to the right of the last closing bracket in the formula

Now hold down SHIFT and CTRL and hit ENTER

Formula should now appear as

{=SUMIF(($A$2:$A$101=$A105),($C$2:$G$101)))}

If you have the curly brackets and you're still getting an error then let us know

PS. Presuming you're not working on a Mac ?

 

[singingsister]

Yey! It Works! Woohoo!
Thanks!