Thread: formula to add unique values

here's the situation and what i'm trying to do:

each row/record is as follows:

column A contains zip code
column B contains zip code population
column C contains city name
columns D contains state name

there are multiple duplicates in each column.

i am trying to calculate the population for each city and place that data in each row in column E.

in order to do so, i need to add the populations (column B) of all UNIQUE zip codes (column A) for each city (column B) / state (column D).

any help would be greatly appreciated!

Welcome to the forum,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Try something like:

=SUMPRODUCT(--($A$2:$A$100=A2),--($C$2:$C$100=C2),--($D$2:$D$100=D2),$B$2:$B$100)

adjust ranges to suit your database...

Note: You can also create a Pivot table to do this... FYI: Pivot Tables

My assumption was that you just wanted to show the total quantity in each row... so at duplicate zip/states, you would have the same total quantity displayed....

... also, again, the Pivot table would relist the items, uniquely, and show the grand total for each unique item.

First, you need a list of unique city/state in the new sheet...

... so go to the new sheet and select where you want the list to go, then go to Data|Filter|Advanced Filter.

Click the icon to the right of list range and then select column B on the original sheet... click the icon again to go back.

Select Copy to another location and in the Copy To field enter the top cell in the range you want to copy to in the current sheet.

Check Unique Records Only.

Click Ok.

Now if you have a unique list use formula like:

=INDEX(Sheet2!$D$1:$D$100,MATCH(TRUE,INDEX(Sheet2!$B$1:$B$100=A2,0),0))

where Sheet2 contains your original database adjust ranges and copy down...

... then if that works, let me know where to go from there....

where Sheet2 i

Originally Posted by NBVC

My assumption was that you just wanted to show the total quantity in each row... so at duplicate zip/states, you would have the same total quantity displayed....

Perhaps I'm missing something but wouldn't your formula just show, for each zip, the population for that zip code multiplied by the number of times that code appears in the data? I'm not sure what value that would have.

My formula should give a total population for each city, considering all zip codes listed but not double counting any (of course it shows the same population against every row for that city and state).

I guess I assumed the OP wanted to see at each line, what the total population for that zip code in that line is (sum of all occurrances of that zip code).... so there may be repeated total population sums...

...thinking about it.. you probably can just use zip code, since they should be unique to city/state.. so =Sumif(A:A,A2,B:B) would probably give same answer...

... I could be wrong in my interpretation... highly likely that is the case....

Ok, I thought by lowest row.. you meant smallest row number (i.e. first occurance of each)..

To get last occurance, Try in Sheet2:

=Lookup(2,1/('Sheet1'!$A$2:$A$100=A2),'Sheet1'!$B$2:$B$100)

again adjusting the ranges to suit and it assumes you are starting at row 2...

You are quite welcome, can you remember to please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save