CODEWITHSUNDEEP
excel
Tolga
Tolga

Reputation: 1357

Excel formula - auto sum for the same types

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

Upvotes: 11

Views: 44935

Answers (4)

ASH
ASH

Reputation: 20302

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/

Upvotes: 1

adeel
adeel

Reputation: 31

  A        B

1 Type1 10

2 Type2 10

3 Type2 10

4 Type1 10

=Sumif(A1:A4,"=Type1",B1:B4)

Upvotes: 3

Simon
Simon

Reputation: 80759

I think sumif is what you are looking for

Upvotes: 8

jakber
jakber

Reputation: 3569

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.

Upvotes: 14

Related Questions