# Add a condition to a SUMPRODUCT formula

#### Fin Fang Foom

##### Well-known Member
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).

##### Board Regular
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
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
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?

##### MrExcel MVP
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
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
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
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
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

##### Well-known Member
, do you want to divide by 1 so that the sum will be available for further calculations

Yes.

Replies
0
Views
123
Replies
0
Views
130
Replies
17
Views
351
Replies
4
Views
176
Replies
1
Views
225

### Forum statistics

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.

### Which adblocker are you using?

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

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