IF

[singingsister]

I have a sales spreadsheet of orders broken down into areas of the country (ie GB1 is one part of the UK, GB2 another and so on).

This is how the spreadsheet looks...

Area Customer Week 37 week 38 week 39
GB1 J Bloggs £55 £564.00
GB2 M Smith Valve £246.00
GB1 Times Paper £1115.08
GB3 xyz £489.61 £46.84

What I want to do is to total all orders placed for each area
I'm sure it's something like IF... but I'm not sure.

Can someone help me?

 

[Richard Schollar]

Hi

Something like this:

Excel Workbook
	A	B	C	D	E
1	Area	Customer	Week37	week38	week39
2	GB1	JBloggs	55	564.00
3	GB2	MSmithValve	246.00
4	GB1	TimesPaper	1,115.08
5	GB3	xyz	489.61	46.84
6
7
8	GB1	1,734.08
9	GB3	536.45
Sheet2

 

[singingsister]

afraid i can't download anything to work laptop so can't use HTML maker.

richardschollar has it right tho....I'm going to give a go what he suggested.

 

[chrysti]

=SUMIF(RANGE, CRITERIA, SUM RANGE)
RANGE = The areas GB1, GB2 etc.
CRITERIA = which area you are summing for ex GB1
SUM RANGE = the week that you want to add up

Hope this helps
Chrysti

 

[singingsister]

chrysti,

i don't get the sum range bit....i need to add up (for example) all the GB1 orders on all weeks on the spreadsheet - what goes in the sum range bit?

 

[chrysti]

SUM RANGE = the column that you want to add together. If you want week 36 in column D then you would select D1:D100 or whatever you have data in that you want to sum. This is where the other pieces of the forumla look it...basically it says if I am GB1 and I have sales in Column D add me to any other cell that is GB1 with sales in Column D.

Hope this helps...just an FYI make sure that you RANGE and SUM RANGE are the same...if your RANGE is from A1:A100 then your SUM RANGE has to be D1:D100 if you start or end in a different cell count then it throws off the results.

You just repeat the formula for each week

Chrysti

 

[singingsister]

Chrysti

thanks - but the thing is I want to add all weeks up together - not seperately. So for example week 36 to week 39.

 

[chrysti]

=sumif(A1:A100,"GB1",B1:B100)+sumif(A1:A100,"GB1",C1:C100)+sumif(A1:A100,"GB1",D1:D100)+sumif(A1:A100,"GB1",E1:E100)

This would be the easiest way to use the SUM IF formula to do what you want

Chrysti

 

[singingsister]

nope it doesn't work!

The column with the area in is A4 through to A216, the column with the customer name (not needed for figures as only broked down by area) is B4 to B216, and the weeks with the values of orders placed for each customer is C,D,E and F 4 to 216 (weeks 36 to 39).

 

[Richard Schollar]

Have you tried my formula?