CODEWITHSUNDEEP
excelexcel-formula
Saeed
Saeed

Reputation: 4125

How to multiply B1 with C1, B2 with C2, Bn with Cn and Sum them?

This is my sheet:

Goods Type  Quantity / Geram    Gold Price 750K Price of Exchange
Gold        100                 1,554.00        0
Silver      500                 235.00          0
Euro        200                 0.00            1.01
Pond        50                  0.00            0.97
Gold        100                 1,554.00        0
Silver      500                 235.00          0
Euro        200                 0.00            1.00
Pond        50                  0.00            0.99

I want to do to this:

If Goods Type is Gold: multiply Quantity / Geram by Gold Price.

In my case it should be: (100 * 1554) + (100 * 1554) = 310800, and if I add more entries in the future, it should add more.

I have this cell in my current sheet: If Goods Type is Gold, then sumif all Quantity / Geram in another cell which is now 200.

Upvotes: 0

Views: 104

Answers (2)

Dominique
Dominique

Reputation: 17551

You can make a SumProduct() for three columns, so you can create an IF() formula for getting 1 in case of "Gold" and 0 else. This leads to following kind of formula:

=SUMPRODUCT(IF(A2:A6="Gold",1,0),B2:B6,C2:C6)

See following screenshot:

enter image description here

Upvotes: 2

user11222393
user11222393

Reputation: 5501

Use SUMPRODUCT: sumproduct

=SUMPRODUCT((A2:A9="Gold")*B2:B9*C2:C9)

It multiplies TRUE/FALSE (result of A2:A9="Gold") with quantity and price and sums everything (in this case = 155400 + 0 + 0 + 0 + 15540 + 0 + 0 + 0))

Result:

enter image description here

Adjust ranges to your data.

Upvotes: 2

Related Questions