SUM of areas

  • Thread starter Thread starter Natalie
  • Start date Start date
N

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
 
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
 
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)
 
How about SUBTOTAL? If you want to go this route, first sort your data and
use subtotal command in the menu
 
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
 
Back
Top