Add a condition to a SUMPRODUCT formula

Fin Fang Foom

Well-known Member
Joined
Mar 20, 2005
Messages
598
Well I have another problem. I would like add a condition to this formula to look for a paticular machine. So I convert this formula:

=SUMPRODUCT((B3:B7+D3:D7),(B3:B7+D3:D7)/C3:C7)/(SUM(B3:B7)+SUM(D3:D7))

into this

=SUMPRODUCT((A$3:A$30=E3)*(B$3:$B$30+$D$3:$D$30),($B$3:$B$30+$D$3:$D$30)/$C$3:$C$30)/(SUM($B$3:$B$30)+SUM($D$3:$D$30))

But it did not work.

Any Ideas?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

galadiman

Board Regular
Joined
Apr 4, 2007
Messages
66
Try this: (amateur, but I think i got it):

=SUMPRODUCT(--(A$3:A$30=E3),(B$3:$B$30+$D$3:$D$30),($B$3:$B$30+$D$3:$D$30)/$C$3:$C$30)/(SUM($B$3:$B$30)+SUM($D$3:$D$30))
 

Fin Fang Foom

Well-known Member
Joined
Mar 20, 2005
Messages
598
Try this: (amateur, but I think i got it):

=SUMPRODUCT(--(A$3:A$30=E3),(B$3:$B$30+$D$3:$D$30),($B$3:$B$30+$D$3:$D$30)/$C$3:$C$30)/(SUM($B$3:$B$30)+SUM($D$3:$D$30))


It did not work. It gives me the #DIV/0! error. I think because there are blanks in the table. I tried to modified this formula but no luck.

=SUMPRODUCT(($A$3:$A$19=E3)*(B3:B19<>"")*($B$3:$B$19+$D$3:$D$19),($B$3:$B$19+$D$3:$D$19)/$C$3:$C$19)/(SUMPRODUCT(($A$3:$A$19=E3)*(B3:B19<>"")*($B$3:$B$19))+SUMPRODUCT(($A$3:$A$19=E3)*(B3:B19<>"")*($D$3:$D$19)))

Any Ideas?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,652
Office Version
  1. 365
Platform
  1. Windows
Try...

=SUM(IF($A$3:$A$30=E3,($B$3:$B$30+$D$3:$D$30)*(($B$3:$B$30+$D$3:$D$30)/$C$3:$C$30)))/(SUMIF($A$3:$A$30,E3,$B$3:$B$30)+SUMIF($A$3:$A$30,E3,$D$3:$D$30))

...confirmed with CONTROL+SHIFT+ENTER. Note, however, the formula will return #DIV/0! if a row meets the criteria and the corresponding value in Column C is 0 or the cell is empty. Is going to be an issue?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
E3 should be: 3 ( SMALL )

F3:

Control+shift+enter...

=SUM(IF($A$3:$A$19=E3,($B$3:$B$19+$D$3:$D$19)*($B$3:$B$19+$D$3:$D$19)/$C$3:$C$19))/SUM(IF($A$3:$A$19=E3,$B$3:$B$19+$D$3:$D$19))

then copy down.
 

Fin Fang Foom

Well-known Member
Joined
Mar 20, 2005
Messages
598
E3 should be: 3 ( SMALL )



Yes your correct. Thank you Aladin for the formula it works well.



Note, however, the formula will return #DIV/0! if a row meets the criteria and the corresponding value in Column C is 0 or the cell is empty. Is going to be an issue?

Thank you for the formula Domenic it works well. To your question yes that will be an issue thank you foreseen that. Should I add this in the condition as well?

IF(C3:C30<>0,IF(C3:C30<>""

Or that wont give me the correct result?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,652
Office Version
  1. 365
Platform
  1. Windows
Thank you for the formula Domenic it works well.

You're very welcome!

To your question yes that will be an issue thank you foreseen that. Should I add this in the condition as well?

IF(C3:C30<>0,IF(C3:C30<>""

Or that wont give me the correct result?

What if the corresponding value in Column C is 0 or the cell is empty? Instead of dividing by 0 should it divide by 1?
 

Fin Fang Foom

Well-known Member
Joined
Mar 20, 2005
Messages
598
What if the corresponding value in Column C is 0 or the cell is empty? Instead of dividing by 0 should it divide by 1?

I'm sorry I'm not following can you post a example of both scenarios?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,652
Office Version
  1. 365
Platform
  1. Windows
I'm sorry I'm not following can you post a example of both scenarios?

For this part of the formula...

($B$3:$B$19+$D$3:$D$19)/$C$3:$C$19)

...Column B and D are summed, then this sum is divided by Column C. If Column C contains a 0 or is empty, then the sum will be divided by 0 and the formula will return #DIV/0!. So for any row where this is the situation, do you want to divide by 1 so that the sum will be available for further calculations or do you want to ignore the calculation for that row?
 

Forum statistics

Threads
1,181,717
Messages
5,931,641
Members
436,796
Latest member
parthibanmuniswamy

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

Which adblocker are you using?

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
Top