SUM of areas

[Natalie]

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

 

[Bob Phillips]

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

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

 

[Shane Devenshire]

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

 

[Eduardo]

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)

 

[T. Valko]

Use SUMIF

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

D2 = AB

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

 

[Teethless mama]

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

 

[Bernd P]

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

 

[Max]

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,ROWS($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