Sum Product with multiple criteria

Coach Hager

New Member
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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.

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.

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.

Works perfect!

Thanks

Replies
6
Views
173
Replies
1
Views
238
Replies
18
Views
637
Replies
4
Views
187
Replies
1
Views
176

Threads
1,203,137
Messages
6,053,714
Members
444,681
Latest member
Nadzri Hassan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back