CODEWITHSUNDEEP
excelexcel-formula
dreadnought303
dreadnought303

Reputation: 157

Excel - sum values based on distinct value in another column

I have the following structure in an Excel worksheet:

ARTICLE     VALUE
-------     -----
   A         300
   B         100
   A         300
   C         300
   D         200
   A         300

I can always expect that the value for a similar article will be the same. Therefore, the value of A will always be 300. However, other articles could very well have a similar value as a previous article. Hence, C also has a value of 300.

In this case, I am trying to find the sum of unique articles, but only once in case of duplicates. So, in the above example, I need the sum as A (only once), B, C and D - which comes to 300 + 100 + 300 + 200 = 900

I found a COUNTIF formula but this only sums articles which do not have any duplicates (i.e. B, C and D)

{=SUM(IF(COUNTIF(A:A, A:A) = 1, B:B, 0))}

Am I barking up the wrong tree with COUNTIFs?

Upvotes: 4

Views: 11805

Answers (1)

CallumDA
CallumDA

Reputation: 12113

You were close, and COUNTIF is the right way to go. Try this:

=SUMPRODUCT(B1:B6/COUNTIF(A1:A6,A1:A6))

I replaced SUM with SUMPRODUCT so that you don't have to use Ctrl+Shift+Enter

enter image description here

Upvotes: 3

Related Questions