Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from May 9 '13 at 13:57

This question came from our site for professional and enthusiast programmers.

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.

share|improve this answer
@ 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 '13 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 '13 at 12:44

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)
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.