I need a productif type function

M

Mitch

I want to search a range of cells, say C:C for a value, if that value is
there i want to multiply the corresponding B cell and D cell together then
sum up all of the true values. If anyone can help me please let me know.

Thanks
 
A

Ardus Petus

=SUMPRODUCT((C1:C999="something")*(B1:B999)*(D1:D999)

Unfortunately, SUMPRODUCT does not work with whole columns (C:C)

HTH
 
M

Mitch

That seems to work, the only problem is that some of the B and D cells have
no value so this functions returns a #VALUE! error. Thanks for your help
 
B

Bob Phillips

It isn't no value that would cause this. It must be a #VALUE or text in the
cells. Either sort the data or try

=SUMPRODUCT(--(C1:C999="something"),(B1:B999),(D1:D999))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
M

Mitch

That works just how I wanted it...thanks a ton!
Bob Phillips said:
It isn't no value that would cause this. It must be a #VALUE or text in the
cells. Either sort the data or try

=SUMPRODUCT(--(C1:C999="something"),(B1:B999),(D1:D999))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
Click to expand...
 
M

Mitch

I ended up using this
=IF(SUMIF(C2:C41,"Winter 2006",E2:E41)>0,(SUMPRODUCT(--(C1:C41="Winter
2006"),(B1:B41),(E1:E41))/SUMIF(C1:C41,"Winter 2006",B1:B41)),"")

that included a dividing by the same sums of the B values and it works
great. Thanks.
 
Top