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:and copy down. This "marks" the values to be added. Then in another cell: