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?

 

[singingsister]

richardschollar - yes - and for some reason couldn't get it to work!

 

[Richard Schollar]

What was the exact formula you were using? Does it return an incorrect number (eg 0) or an error value?

 

[singingsister]

I'm using
=sumproduct((a2:$212)=a219*$c$2:$f$212)

a2 to 212 is where the area code lies,

a219 is the cell with GB1 that it needs to put the answer next to
c2 to f212 is where the weeks are

it doesn't like the "a2" part of the formula.

also, why does the formula have the * in? What does that do?

 

[Richard Schollar]

If that really is the formula then part of the problem is that you are not giving the column in the first part ie:

a2:$212

should be:

$A$2:$A$212

Also there are parentheses put in incorrect places:

=sumproduct(($a$2:$A$212=a219)*$c$2:$f$212)

The * is the multiplication operator (which I'm sure you knew). So it is being used to multiply the two arrays together.

 

[singingsister]

i copied the formula you gave me AND IT WORKS!

woohoo!

Thanks.
x