SUMPRODUCT issues

[nujwaan]

Hey guys,

So here's my issue. I've got a table which I am using a SUMPRODUCT formula to pull back a value from one sheet called Raw in say column P based on the columns D and E to another sheet.

In this case column P is a formula working out cost per unit and so the formula in P4 is '=E4/F4'. When I try and get this value I get a DIV/0! error. This is even though there is a value of $0.07 in Cell P4 that should be pulled back.

Here is the formula I am using:

=SUMPRODUCT(('Raw'!$D$4:$D$1048576=$A5)*('Raw'!$E$4:$E$1048576=Sheet5!D$33)*'Raw'!$P$4:$P$1048576)

Any ideas??

 

[Aladin Akyurek]

Hey guys,

So here's my issue. I've got a table which I am using a SUMPRODUCT formula to pull back a value from one sheet called Raw in say column P based on the columns D and E to another sheet.

In this case column P is a formula working out cost per unit and so the formula in P4 is '=E4/F4'. When I try and get this value I get a DIV/0! error. This is even though there is a value of $0.07 in Cell P4 that should be pulled back.

Here is the formula I am using:

=SUMPRODUCT(('Raw'!$D$4:$D$1048576=$A5)*('Raw'!$E$4:$E$1048576=Sheet5!D$33)*'Raw'!$P$4:$P$1048576)

Any ideas??

In P4 try:

=IF(N(F4),E4/F4,0)

You could switch here to SumIfs, which is faster...

=SUMIFS('Raw'!$P$4:$P$1048576,'Raw'!$D$4:$D$1048576,$A5,'Raw'!$E$4:$E$1048576,Sheet5!D$33)

 

[MrKowz]

If there are any errors in the ranges that SUMPRODUCT looks at, then the entire formula will error.

I would recommend correcting the #DIV/0 errors by changing their formulas to:

=IF(F4=0,"",E4/P4)

 

[nujwaan]

Amazing!!! What does the N function do?

It worked!!! :D

 

[MrKowz]

=N() converts a non-number value to a number, dates to serials, TRUE to 1, and anything else to 0.

 

[nujwaan]

You guys are geniuses!! (genii??)

And now I look like one at work too! lol

Thank you again.

 

[T. Valko]

Hey guys,

So here's my issue. I've got a table which I am using a SUMPRODUCT formula to pull back a value from one sheet called Raw in say column P based on the columns D and E to another sheet.

In this case column P is a formula working out cost per unit and so the formula in P4 is '=E4/F4'. When I try and get this value I get a DIV/0! error. This is even though there is a value of $0.07 in Cell P4 that should be pulled back.

Here is the formula I am using:

=SUMPRODUCT(('Raw'!$D$4:$D$1048576=$A5)*('Raw'!$E$4:$E$1048576=Sheet5!D$33)*'Raw'!$P$4:$P$1048576)

Any ideas??

You may have other cells in col P that contain #DIV/0! errors.

Try this...

=SUMIFS(Raw!P:P,Raw!D:D,A5,Raw!E:E,Sheet5!D33,Raw!P:P,"<1E100")

 

[Aladin Akyurek]

You guys are geniuses!! (genii??)

And now I look like one at work too! lol

Thank you again.

You are welcome. Thanks for providing us feedback.