Excel - sum values based on distinct value in another column

Question

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?

CallumDA CallumDA · Accepted Answer · 2018-03-07T10:38:13

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

Excel - sum values based on distinct value in another column

1 Answers