Excel formula – auto sum for the same types

calendar_today Asked Dec 22, 2009
thumb_up 8 upvotes
history Updated April 16, 2026

Question posted 2009 · +6 upvotes

I need a quick an simple excel formula to get the sum of values for different types of objects as listed below:

Type1  10 
Type1  10 
Type1  10 
Type2  10 
Type2  10 
Type2  10 
Type2  10 
Type3  10 
Type3  10

Number of items and number of types are unknown (long list), in a different worksheet I would like to get sum of types like:

Sumof Type1: 30
Sumof Type2: 40
Sumof Type3: 20

I need no VBA, just simple excel formula please..

BR

Accepted answer +8 upvotes

Use a pivot table for this.

  1. Add a header row to your data (“data type”, “value”)
  2. Select your data
  3. Insert pivot table
  4. Drag “data type”-header to the Row Labels area
  5. Drag “value”-header to the Values area
  6. Make sure it says “Sum of value” and not “Count of value” in the Values area, if not you need to double-click it and change to use sum.

You could also use the “Add subtotals” feature for this, but pivot tables are more flexible and powerful.

Top excel Q&A (6)

+8 upvotes ranks this answer #106 out of 167 excel solutions on this site .
excel