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?

 

[Domenic]

Try...

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

...confirmed with CONTROL+SHIFT+ENTER. You'll notice that I adopted Aladin's formula, using SUM(IF(...)) instead of two SUMIF's at the end of the formula. I guess it must be more efficient. As usual, I follow his lead... :wink:

Hope this helps!

 

[Fin Fang Foom]

Try...

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

...confirmed with CONTROL+SHIFT+ENTER. You'll notice that I adopted Aladin's formula, using SUM(IF(...)) instead of two SUMIF's at the end of the formula. I guess it must be more efficient. As usual, I follow his lead... :wink:

Hope this helps!

Perfect Thank You so much!!