How to merge and sum 2 arrays using excel?

Question

Given a .csv data file as follows

  12,green orange
  1,good egg
  ...
  5,green orange

I want to sum-up the first column elements if the second column element is the same. Given the example above, now we should have

  17,green orange
   1,good egg
   ...

What is the easiest way to do this?

Also assuming you can import the CSV to Excel without issue. — James Jenkins
– James Jenkins, Commented Mar 10, 2013 at 0:44

enderland · Accepted Answer · 2013-03-10 14:59:27Z

1

A good option would be to use a Pivot Table (this is practically what they are designed for).

In your case, look into the SUMIF formula - link as it doesn't seem you will need such advanced functionlaity.

Copy the list of "keys" (the items) into a separate worksheet and remove duplicates, then just setup SUMIF like:

key   |   total sum
green |   = SUMIF(dataSheet!B:B, A2, dataSheet!A:A)

where you have this in a sheet, and dataSheet represents the CSV values you just imported.

answered Mar 10, 2013 at 14:59

enderland

14.3k18 gold badges105 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Osiris Xu Over a year ago

That's very nice. I worked over the same datasheet, and the data is begin in the first row, so I use =SUMIF(B:B, B1,A:A ), and then sort and remove duplicates.

James Jenkins · Accepted Answer · 2013-03-10 00:43:00Z

1

Assuming you don't want to work with variables.

Count   |Color
12  |Green
1   |Blue
5   |Green 
7   |Green
3   |Blue

Sort the file by the value "Color"

Count   |Color
1   |Blue
3   |Blue
12  |Green
7   |Green
5   |Green

Put Formula in cell C2 and paste down

=IF(B2=B1,(C1+A2),A2)

Harvest the sums at the end of each color (i.e. Blue = 4, Green = 24)

Blue

answered Mar 10, 2013 at 0:43

James Jenkins

1,9632 gold badges24 silver badges44 bronze badges

Collectives™ on Stack Overflow

How to merge and sum 2 arrays using excel?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related