Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel formula - auto sum for the same types

Tags:

excel

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

like image 675
Tolga Avatar asked Dec 22 '09 15:12

Tolga


2 Answers

I would set it up like this.

enter image description here

=SUMPRODUCT(--(D1=A:A),--(E1=B:B),B:B)

So, basically . . .

To count Apples sales for North:
=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"))

or

=SUMPRODUCT((A2:A12="north")*(B2:B12="apples"))

To sum Apples sales for North:
=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12)

See the link below for more details.

https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/

like image 82
ASH Avatar answered Oct 06 '22 00:10

ASH


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.

like image 32
jakber Avatar answered Oct 06 '22 00:10

jakber