Sum of values in column based on unique IDs in another column

[makaveeti]

Hi All,

Sample worksheet attached with expected results.

I require a formula that will do a sum of the values in column B but only for the unique values in Column A where the total tokens > 1. Column A contains duplicate values.

For example if the same ID from column A appears twice with 2 tokens on each row. The total tokens for this user is 2, not 4.

In the sample worksheet there are 4 unique ID's where the total tokens >1. So the sum of tokens is 9.

[ELeGault]

Since you are in 365, Use logic that handles these old problems!

Please Login or Register  to view this content.

--------- Just read your post a little more - that is not your solution... will take another look in a minute - Work calls

[ELeGault]

Try this

Please Login or Register  to view this content.

[ELeGault]

Let me ask, would an ID appear more than once with different values?
In your sample data, what I provided will always work... but if ID 1234 has 2, and then 2, and then 1... the 1 will be lost

[TMS]

Maybe:

Please Login or Register  to view this content.

[makaveeti]

ELeGault - An ID will never appear more than once with different values. Your solution works.

TMS - Your solution also works however I think it is dependant on columns A and B being next to each other. In my real data the columns aren't next to each other which causes the formula to fail.


I updated the sample data slightly to include another condition that I missed from post #1. Column P must also = yes.

So in summary a Sum of the values in column K but only for the unique values in Column A where the values in Column K > 1 AND Column P = Yes.

[AliGW]

Try this adaptation:

=SUM(FILTER(UNIQUE(FILTER(A2:K23,K2:K23>1)),{0,0,0,0,0,0,0,0,0,0,1}))

I am sure you have been told before that it is ESSENTIAL that you present REALISTIC data from the outset - ANY change in layout or detail you've missed leads to goalposts being constantly shifted, which is very frustrating for your helpers and means a lot of time wasted.

PS Maybe this is what you want?

=SUM(FILTER(UNIQUE(FILTER(A2:P23,(K2:K23>1)*(P2:P23="YES"))),{0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0}))

Or this?

=COUNT(UNIQUE(FILTER(A2:A23,(K2:K23>1)*(P2:P23="YES"))))

[Glenn Kennedy]

or in case all those zeros make your eyes water...


=SUM(INDEX(UNIQUE(FILTER(A2:P23,(K2:K23>1)*(P2:P23="YES"))),,11))

[AliGW]

Glenn - the one with the zeros doesn't actually return the right answer. I think my final offering does, though.

[TMS]

@makaveeti:

TMS - Your solution also works however I think it is dependant on columns A and B being next to each other. In my real data the columns aren't next to each other which causes the formula to fail.

As Ali has pointed out, you need to post a realistic representation of your data. No-one but you knows what it really looks like. You can only get working solutions based on what you share.

[makaveeti]

AliGW & TMS - Apologies and understood. Sometimes I overlook subtleties which I don't think would be an issue however this thread proves I shouldn't.


AliGW - The second formula works - =SUM(FILTER(UNIQUE(FILTER(A2:P23,(K2:K23>1)*(P2:P23="YES"))),{0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0}))

Glenn Kennedy - Your solution also works great.

Marking thread as solved.

[AliGW]

So the expected result was not 9, nor was it 3 (as suggested in the workbook), but 7 ...

You managed to make this all very confusuing, but glad you got there in the end. Think a bit more carefully before posting a new thread, please. Thanks.