SUM of areas
 

Hi there, I have a list of invoices with postcodes, and I want to know the
amount per postcode. For example.

Column A Column B
Postcode Invoice amount
AB £600
CH £40
AB £50
CH £30
SO £100
SG £40
SO £200
CH £300

And I want to know the total of each postcode but without using a pivot chart?
Can anyone help? I think I need SUMPRODUCT but I'm not sure.

Thanks

Natalie

SUM of areas
 

=SUMIF(A:A,"AB",B:B)

You van put AB in a cell and reference that cell as well.

--
__________________________________
HTH

Bob

"Natalie" wrote in message
...
Hi there, I have a list of invoices with postcodes, and I want to know the
amount per postcode. For example.

Column A Column B
Postcode Invoice amount
AB £600
CH £40
AB £50
CH £30
SO £100
SG £40
SO £200
CH £300

And I want to know the total of each postcode but without using a pivot
chart?
Can anyone help? I think I need SUMPRODUCT but I'm not sure.

Thanks

Natalie

SUM of areas
 

Hi,

Try

=SUMIF(A$1:A$10,D1,B$1:B$10)

where the postal codes are in column A the amts in column B and the first
postal code you want to sum in D1

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Natalie" wrote:

Hi there, I have a list of invoices with postcodes, and I want to know the
amount per postcode. For example.

Column A Column B
Postcode Invoice amount
AB £600
CH £40
AB £50
CH £30
SO £100
SG £40
SO £200
CH £300

And I want to know the total of each postcode but without using a pivot chart?
Can anyone help? I think I need SUMPRODUCT but I'm not sure.

Thanks

Natalie

SUM of areas
 

Hi,
Let's assume you have a list of your Post codes in column C starting in C1
in D1 enter

=sumproduct(--(C1=$A$1:$A$100),$B$1:$B$100)

change the range to fit your needs but remember the range has to be the same
in both sides of the formula

if you are using excel 2007

=sumproduct(--(C1=A:A),B:B)

"Natalie" wrote:

Hi there, I have a list of invoices with postcodes, and I want to know the
amount per postcode. For example.

Column A Column B
Postcode Invoice amount
AB £600
CH £40
AB £50
CH £30
SO £100
SG £40
SO £200
CH £300

And I want to know the total of each postcode but without using a pivot chart?
Can anyone help? I think I need SUMPRODUCT but I'm not sure.

Thanks

Natalie

SUM of areas
 

Use SUMIF

=SUMIF(A$2:A$10,"AB",B$2:B$10)

D2 = AB

=SUMIF(A$2:A$10,D2,B$2:B$10)

--
Biff
Microsoft Excel MVP


"Natalie" wrote in message
...
Hi there, I have a list of invoices with postcodes, and I want to know the
amount per postcode. For example.

Column A Column B
Postcode Invoice amount
AB £600
CH £40
AB £50
CH £30
SO £100
SG £40
SO £200
CH £300

And I want to know the total of each postcode but without using a pivot
chart?
Can anyone help? I think I need SUMPRODUCT but I'm not sure.

Thanks

Natalie

SUM of areas
 

How about SUBTOTAL? If you want to go this route, first sort your data and
use subtotal command in the menu


"Natalie" wrote:

Hi there, I have a list of invoices with postcodes, and I want to know the
amount per postcode. For example.

Column A Column B
Postcode Invoice amount
AB £600
CH £40
AB £50
CH £30
SO £100
SG £40
SO £200
CH £300

And I want to know the total of each postcode but without using a pivot chart?
Can anyone help? I think I need SUMPRODUCT but I'm not sure.

Thanks

Natalie

SUM of areas
 

Hello Natalie,

If you do not want to update your formulas if a new postcode is added
(or an old one is deleted), I suggest to use my UDF Sfreq or Pstat:
http://sulprobil.com/html/pstat.html
http://sulprobil.com/html/sfreq.html

Regards,
Bernd

SUM of areas
 

Another formulas play which will deliver the list of unique postcodes
and their corresponding sums dynamically

Source data as posted assumed in A2:B2 down,
where col B is presumed to contain real numbers

In C2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",ROW()))
Leave C1 empty. This is a criteria col for deriving uniques

In D2:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))))

In E2: =IF(D2="","",SUMIF(A:A,D2,B:B))
Select C2:E2, copy down to cover the max expected extent of source data, say
down to E200?. Hide/min col C. Col D will continuously return the list of
unique postcodes dynamically as source data is progressively updated while
col E returns the corresponding sums for the invoice amounts

Wave your success? Click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Natalie" wrote:
Hi there, I have a list of invoices with postcodes, and I want to know the
amount per postcode. For example.

Column A Column B
Postcode Invoice amount
AB £600
CH £40
AB £50
CH £30
SO £100
SG £40
SO £200
CH £300

And I want to know the total of each postcode but without using a pivot chart?
Can anyone help? I think I need SUMPRODUCT but I'm not sure.

Thanks

Natalie