Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total A Range
I have a list of over 3000 people that bought products in different zip codes.
The spreadsheet is set up like below: 30044 1 30044 1 30125 1 11253 1 Th first numbers are the zip code and in the cell next to it, the number 1 represents a boought product in that zip code. Being that I have a list of over 3000, how can I make a function that will total up how many products were sold in each zip code??? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total A Range
Produce a unique list of zip codes on another sheet. To do this,
insert a new sheet, and copy the zip codes from your existing sheet to column A of the new sheet. If you do not have a header, then insert a new row 1 and put "zip code" in A1. Then highlight all the data in column A including the header, click on Data | Filter | Advanced Filter and select Unique Records Only, Click on Copy to another location, and enter C1 as the location. Click OK and you will have a unique list in column C - you can delete columns A and B so that your unique list is now in column A. Put "Total" in B1 of this new sheet and this formula in B2: =SUMIF(Sheet1!A$1:A$3500,A2,Sheet1!B$1:B$3500) and then just copy this down to cover your unique list. Hope this helps. Pete On Jan 17, 5:24*pm, Hamed parhizkar wrote: I have a list of over 3000 people that bought products in different zip codes. The spreadsheet is set up like below: 30044 * 1 30044 * 1 30125 * 1 11253 * 1 Th first numbers are the zip code and in the cell next to it, the number 1 represents a boought product in that zip code. Being that I have a list of over 3000, how can I make a function that will total up how many products were sold in each zip code??? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total A Range
Assuming your data starts in Row 2 (zip codes in Column A, product count in
Column B), put this... =IF(OR(COUNTIF($A$2:A2,A2)1,B2=""),"",SUMIF(A:A,A 2,B:B)) in Row 2 of an unused column and then copy it down for as many cells as you want (even past your last piece of data)... it will show the total of a particular zip code next to the first occurrence of that zip code in you list. Rick "Hamed parhizkar" wrote in message ... I have a list of over 3000 people that bought products in different zip codes. The spreadsheet is set up like below: 30044 1 30044 1 30125 1 11253 1 Th first numbers are the zip code and in the cell next to it, the number 1 represents a boought product in that zip code. Being that I have a list of over 3000, how can I make a function that will total up how many products were sold in each zip code??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding 'total' in range | Excel Worksheet Functions | |||
how to get a total to add up a range | Excel Worksheet Functions | |||
finding a range of dates to total | Excel Discussion (Misc queries) | |||
Searching a range of numbers for a specified total | Excel Worksheet Functions | |||
How do I get the total of a range of cells that are in another she | Excel Discussion (Misc queries) |