I need to look up names from column 1 then sum numbers from column 2. Some names in column one appear more then once. (The spread sheet I am working on has 7400 rows!!!)

Example:

 Column A   ColumnB

 Nad         10      
 Baz         15
 Nad         50
 Nad         5
 Baz         45
 Baz         10
 Ash         15
 Ash         25

How many total numbers for each name. So for Nad it should say: 65

up vote 1 down vote accepted

What you are looking for calls grouped Pivot Table:

In the row and column label areas of a PivotTable report, you can group the items in a field in a custom way. Grouping the data can help you to isolate a subset of data that satisfies your specific needs, and that cannot be easily grouped in other ways, such as sorting and filtering.

enter image description here

Group numeric items

    Select the numeric field in the PivotTable report that you want to group.
    On the Options tab, in the Group group, click Group Field.
    In the Starting at box, enter the first item to group.
    In the Ending at box, enter the last item to group.
    In the By box, type a number that represents the interval included in each group.

What you need is data consolidation.

  1. Click a cell where you want to locate the result in your current worksheet.
  2. Click Data > Consolidate
  3. Select Sum from Function drop down list
  4. In reference field, select source data range (you can select it with column labels as an option)
  5. (if you have selected data with column labels) tick relevant tick boxes: Use labels in Top row and Left column
  6. Click OK

For more detailed description with screenshots, see

  • Thank you. Both options worked. data consolidation and also grouped Pivot Table. – Zak Sep 5 '14 at 12:15

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.