Add a condition to a SUMPRODUCT formula

[Fin Fang Foom]

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?

 

[galadiman]

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]

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]

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]

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]

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]

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]

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]

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?

 

[Fin Fang Foom]

, do you want to divide by 1 so that the sum will be available for further calculations

Yes.