How do I SUM only distinct values depending on criteria?
Solution 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.
Solution 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
Related videos on Youtube
Jeff
Updated on September 18, 2022Comments
-
Jeff over 1 year
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
-
CharlieRB over 8 yearsWelcome to Super User. You have tagged the question with
pivot-table
, but did not ask about pivot tables. What does this have to do with them? Also, we need to know what you have already attempted to resolve this. We will help you from there.
-