Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summarize areas | Excel Discussion (Misc queries) | |||
Vlookup areas? | Excel Worksheet Functions | |||
AREAS function | Excel Discussion (Misc queries) | |||
Benifit of AREAS | Excel Worksheet Functions | |||
printing from different areas | Excel Worksheet Functions |