Productif Function?

I am trying to do a formula that does a multiplication instead of adding it, but there is no such function.

A B C
1 Value Quantity Department
2 $25 5 100
3 $10 6 200
4 $50 1 100


I need a formula that would, return $175 (5 x $25 + 1 x $50) if I want to get the values for department 100, and $60 (6 x $10) if I get the value for deparment 200.

I have more departments (100, 200, 300, 400, 500, 600, and 700), but the above is just an example.

Try

=SUMPRODUCT(--(C2:C4=100),A2:A4*B2:B4)

That's awesome. It worked. I never used the -- before, and I dont know what it is, but I'll figure it out later.

You're welcome.

The -- is called a double unary

It basically multiplies a value by -1, then multiplies by -1 again.

This is used to convert the True/False results of the expression C2:C4=100 to a 1 or a 0 (True=1, False=0)

=sum(if(C:C=100,(A:A)*(B:B)))

use ctrl shift enter to enter the formula...

Say thanks, click *

Originally Posted by Tony Valko

=SUMPRODUCT(--(C2:C4=100),A2:A4,B2:B4)

Where would this be on the speed scale?
=SUMPRODUCT((C2:C4=100)*A2:A4*B2:B4)

Makes sense, let sumproduct do the math itself whenever possible.