[SOLVED]Extract distinct cells and sum values next to

**nsourm** · 03-14-2012, 06:58 AM

Hello to all. I'm having a problem that i can't solve.(i can do it by sorting values, or with pivot table).
I have a table with two columns. In the first column there are cells that appear many times.Next to each cell there is a value.
I want to extract distinct values from the first column and sum next to each distinct extracted cell all the values.

I'm attaching an example

Thanks a lot

**Pete_UK** · 03-14-2012, 07:22 AM

The attached file shows how this can be done. I've inserted a header row and put this array* formula in D2:

=IFERROR(INDEX(A$2:A$21,MATCH(0,COUNTIF($D$1:D1,A$2:A$21),0)),"")

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter rather than the usual <Enter>.

Then I have this formula in E2:

=IF(D2="","",SUMIF(A:A,D2,B:B))

and then both these formulae can be copied down as far as you think you will need (I've copied to row 7).

Hope this helps.

Pete

**nsourm** · 03-14-2012, 07:36 AM

Thanks a lot Pete.
You save my life.

Kind regards
Nick

**Pete_UK** · 03-14-2012, 09:56 AM

You're welcome, Nick.

Perhaps you can mark the thread as Solved, and you can also express your thanks further by clicking on the "Star" icon in the bottom left corner of any thread that has helped you.

Pete