CODEWITHSUNDEEP
excelexcel-formulavba
Osiris Xu
Osiris Xu

Reputation: 713

How to merge and sum 2 arrays using excel?

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?

Upvotes: 0

Views: 975

Answers (2)

enderland
enderland

Reputation: 14145

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.

Upvotes: 1

James Jenkins
James Jenkins

Reputation: 1954

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

Upvotes: 1

Related Questions