How do I SUM only distinct values depending on criteria?

Question

The question is simple but the answer probably complicated. I have lots of data, but to be brief I gather everything in two columns. Column A, contains random names, and column B amounts. Based on the names on Column A, and need to have a SUM from the amounts but only distinct values, and only one SUM per name, not duplicate names.

Example:

So the solution should be:

John      70,000
Mike      60,000
Anna      50,000
Jennifer  40,000

Answer 1

Create a place for your summary of names and sums. Say you placed the names in cells C1-C4 & the sums for the names in D1-D4; then you could use a formula in D1 like the following for a name in C1:

=sumproduct(--(A1:A11=C1),(B1:B11))

Then copy then fill the formula down from D1 through D4. Hopefully you can extrapolate on this for your own needs.

You can also use this add more criteria if needed.

Answer 2

Insert a new row at teh top and create column names say Person and Sales Create a pivot table of the data A1:B12 and display Person as rows and Sum of Sales as the data