Total A Range

[Hamed parhizkar]

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???

 

[Pete_UK]

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

 

[Rick Rothstein \(MVP - VB\)]

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,A2,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