Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Say I have an Excel worksheet with two columns and five rows like this:

Type Value
----------
B    1
A    4
B    2
B    2
A    4

How can I get Excel to aggregate the data for me? I want something like this (SUM):

Type Sum
--------
A    8
B    5

or like this (COUNT):

Type Count
----------
A    2
B    3

Of course, this is just sample data. The real sheet has a few hundred types, each with dozens to hundreds of entries. I don't want to use a macro. I have access to Excel 2010 and Excel 2013.

share|improve this question
add comment

3 Answers 3

up vote 3 down vote accepted

Use Consolidate (Tab Data - Group data Tools)

Reference is your data range

Select use labels in left column

share|improve this answer
    
Got it! I'd been playing with this myself, but never got it to work. I was trying to use the top row for the headers, and was ignoring the value column while working the count first. Using labels from the left column but still counting on the values column and I got what I needed. I also had to sort on the type column first. –  Joel Coehoorn Mar 7 '13 at 17:41
add comment

Check out Pivot Tables! They are by far the greatest feature in Excel - and are just the right tool for your problem:

  1. Insert a pivot table (Insert tab->Pivot table)
  2. Use your data as Data source
  3. Drag the Type field to the Row Labels
  4. Drag the Value field to the Values

Your sum is done! If you want the count instead, double click the Value field in and select Count as the aggregate method. If you want to see both, simply drag the Value field in again and change it to Count.

share|improve this answer
add comment

Try the SUMIF and COUNTIF functions.

For example:

=SUMIF(A1:A5, "=A", B1:B5)

or

=COUNTIF(A1:A5, "=B", B1:B5)
share|improve this answer
    
This means I know what all my types are. Obviously the sample data is just that: a sample. The real data has a few hundred types, each with dozens of entries. I'll update the question. –  Joel Coehoorn Mar 7 '13 at 17:31
add comment

Your Answer

 
discard

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

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