Sum Product with multiple criteria

[Coach Hager]

I have 4 columns of data and several rows

Column A is Type Column B is $'s Column C is Units Column D is Notes

I want to calculate the weighted average of the $'s based of the Units if Column A = "Car" and column D contains the word "discount" anywhere in it.

Is this possible.

Thanks in advance

 

[schielrn]

Maybe try:

=SUMPRODUCT(--($A$2:$A$500="Car"),--($D$2:$D$500="discount"),$B$2:$B$500)/SUMPRODUCT(--($A$2:$A$500="Car"),--($D$2:$D$500="discount"),$C$2:$C$500)

Adjust the ranges as necessary. Hope that helps.

 

[Coach Hager]

That seems to work only if column d only has "discount" in it. Is there a way to include that row if column d has "5% discount" in it or "discount of 2%"?

I need the calculation to include any row that has "discount" anywhere within column D.

 

[schielrn]

ok try:

=SUMPRODUCT(--($A$2:$A$500="Car"),--(ISNUMBER(SEARCH("discount",$D$2:$D$500))),$B$2:$B$500)/SUMPRODUCT(--($A$2:$A$500="Car"),--(ISNUMBER(SEARCH("discount",$D$2:$D$500))),$C$2:$C$500)

Hope that helps.

 

[Coach Hager]

Works perfect!

Thanks