I need a productif type function

[Guest]

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

 

[Ardus Petus]

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

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

HTH

 

[Guest]

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

 

[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),(D1999))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

That works just how I wanted it...thanks a ton!

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),(D1999))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

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.