DanaD

[DanaD]

Hello to all. I have a doozy of an excel question. This is my formula:

=SUMPRODUCT(--('Toyota Sale'!$F$8:$F$71=$A34),--('Toyota Sale'!$D$8:$D$71=$A$9),'Toyota Sale'!$W$8:$W$71)/SUMPRODUCT(--('Toyota Sale'!$F$8:$F$71=A34),--('Toyota Sale'!$D$8:$D$71=A9))

What I now need is to get rid of the #DIV/0! that appears when my target cells are blank. Can anyone do this by modifying my formula?

Thanks for your consideration.

ray:

 

[njimack]

Does this do the trick?
=IF(SUMPRODUCT(--('Toyota Sale'!$F$8:$F$71=A34),--('Toyota Sale'!$D$8:$D$71=A9)),SUMPRODUCT(--('Toyota Sale'!$F$8:$F$71=$A34),--('Toyota Sale'!$D$8:$D$71=$A$9),'Toyota Sale'!$W$8:$W$71)/SUMPRODUCT(--('Toyota Sale'!$F$8:$F$71=A34),--('Toyota Sale'!$D$8:$D$71=A9)),0)

 

[T. Valko]

Hello to all. I have a doozy of an excel question. This is my formula:

=SUMPRODUCT(--('Toyota Sale'!$F$8:$F$71=$A34),--('Toyota Sale'!$D$8:$D$71=$A$9),'Toyota Sale'!$W$8:$W$71)/SUMPRODUCT(--('Toyota Sale'!$F$8:$F$71=A34),--('Toyota Sale'!$D$8:$D$71=A9))

What I now need is to get rid of the #DIV/0! that appears when my target cells are blank. Can anyone do this by modifying my formula?

Thanks for your consideration.

ray:

What version of Excel are you using?

 

[Jonmo1]

Welcome to the board...

Try this

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,SUMPRODUCT(--('Toyota Sale'!$F$8:$F$71=$A34),--('Toyota Sale'!$D$8:$D$71=$A$9),'Toyota Sale'!$W$8:$W$71)/SUMPRODUCT(--('Toyota Sale'!$F$8:$F$71=A34),--('Toyota Sale'!$D$8:$D$71=A9))
))

It will return a 0 instead of #DIV/0!

Hope that helps.

 

[DanaD]

Hello nijimack.

Worked like a charm. I was trying every use of IF,0 but the correct one. You deserve a beer for sure, eh.

Thanks a bunch.

DanaD

 

[DanaD]

Sorry, njimack, not nijimack.

 

[njimack]

Hello nijimack.

Worked like a charm. I was trying every use of IF,0 but the correct one. You deserve a beer for sure, eh.

Thanks a bunch.

DanaD

Glad it worked