Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

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
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

Try the SUMIF and COUNTIF functions.

For example:

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


=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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .