Excel – How to sum only values in one column that have a unique value in another column

microsoft excelworksheet-function

Good afternoon, this is my first post on SU but have used the resource often to help me solve various queries.

I haven't had any luck in finding an answer to the following:

In excel for mac v15 I have a large table with a column I want to sum. However the column has duplicate values in it (for a reason that I can't change). Column A has unique IDs for each row that has distinct data. The ID is repeated for those rows that are duplicates. An example is as follows:

A --------- B ------------ C  
ID --- COLOUR --- VALUE  
1 ----- Orange -------- 4  
2 ------Brown --------- 3  
2 ----- Red ------------ 3  
2 ----- Green --------- 3  
3 ----- Blue ----------- 8  
4 ----- Green --------- 2  
4 ----- Brown --------- 2  
5 ----- Purple --------- 6  
5 ----- Brown --------- 6  
5 ----- Red ------------ 6  
6 ----- Blue ----------- 2

So I am trying to create a formula which will sum Column C, but disregard the rows where Column A has a repeated ID number – the answer in the example would be (4+3+8+2+6+2) = 25

Any help on this would be much appreciated, and if you need any more information then let me know! Thank you.

EDIT: Thank you everyone so far for your help, all suggestions seem to work well. To make things slightly more complicated:

Table 2
I now need to be able to sum Column D if Column E says "Ordered" and then Column B says "Small" while still ignoring the duplicates like before. So in this example the answer would be (4+8+6)=18.

The data for the table comes from a data dump and so there will not always be the same number of rows of data, and there will not always be each text option present in the table. I should have mentioned this first off but thought I might be able to sort the latter part once I knew how to do the first bit!

Thanks again for any help offered! Much appreciated.

Best Answer

We can use a Helper column to avoid array formulas. In D1 enter 1, in D2 enter:

=IF(COUNTIF($A$1:A1,A2)>0,0,1)

and copy down. This "marks" the values to be added. Then in another cell:

=SUMPRODUCT(C:C*D:D)

enter image description here

Related Question