1

I have 2 columns in excel eg:

Component    Size

A1           12
A2           5
A1           6
A3           10
A2           4

I need to get the output as:

A1        18   (sum of the values for A1)
A2        9
A3        10

Can you please suggest a solution without using macro

Improve this question

2 Answers 2

Reset to default
5

Or alternatively, you can use a pivot table.

  1. Select your data table.
  2. Go to Insert > PivotTable
  3. Drag the fields in the 'boxes'; Components in 'Raw Labels' and Size in 'Sum Values'

And you obtain this:

enter image description here

You can copy the result table to another sheet.

Improve this answer
2
  • @ Jerry Thanks for the reply. However, the values in the size column are dynamic. I have to keep summing as and when the size appear on selecting the component (a dropdown) and some other additional parameters .
    – PDM
    May 9, 2013 at 12:28
  • @PDM You can use PivotTable for dynamic ranges as well. You could select the whole columns and you will only need to refresh the pivot table (you can see the refresh button in the picture I posted in my answer). If you add additional columns though, you can edit the PivotTable source by clicking on 'Change Data Source' to the right of 'Refresh'. I can't see your actual data to be sure, but I consider PivotTables easier in this instance.
    – Jerry
    May 9, 2013 at 12:44
3

I've done this a lot. The easiest way is to copy column A containing your keys (A1, A2, etc in your example) to a separate sheet, let's say your data is Sheet1 and this list is Sheet2.

Remove duplicates from this list and sort as desired.

Then use the sumif formula on this second sheet (see here for advanced documentation).

In your case, the formula in Sheet2!B1 would be:

=SUMIF(Sheet1!A:A, A1, Sheet1!B:B)
Improve this answer

You must log in to answer this question.