Very strange SUMPRODUCT failure to return

[JoeJackson12]

Hi All, I have a code that works great for returning information from cells that contain numbers derived from formulas, but not when one of the values can be

""

or

NA()

. The code is

=SUMPRODUCT(($G$6:$M$9)*(F6:F9="butter")*(E6:E9="low"))

The layout looks like:

[TABLE="width: 648"]
<tbody>[TR]
[TD="width: 72, bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#002856]high[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, width: 72, bgcolor: #F3F3F3"][B][FONT=Arial][SIZE=3][COLOR=#002856]eggs[/COLOR][/SIZE][/FONT][/B]
[/TD]
[TD="class: xl66, width: 72, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]33[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, width: 72, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]31[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, width: 72, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]38[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, width: 72, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]46[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, width: 72, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]14[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, width: 72, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]3[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, width: 72, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]21[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#002856]low[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: #F3F3F3"][B][FONT=Arial][SIZE=3][COLOR=#002856]butter[/COLOR][/SIZE][/FONT][/B]
[/TD]
[TD="class: xl66, bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#002856]NA()[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]4[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]34[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]8[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]43[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]31[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]34[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#002856]low[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: #F3F3F3"][B][FONT=Arial][SIZE=3][COLOR=#002856]bacon[/COLOR][/SIZE][/FONT][/B]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]21[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]30[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]39[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]8[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]45[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]47[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]42[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#002856]low[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: #F3F3F3"][B][FONT=Arial][SIZE=3][COLOR=#002856]butter[/COLOR][/SIZE][/FONT][/B]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]11[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]29[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]7[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]25[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]26[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]40[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][FONT=Arial][SIZE=3][COLOR=#002856]8[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]

The NA() makes it not work. I can't put zeros in there because sometimes the actual value is a zero. I also can't put a

""

, that has the same effect as the NA(). The cell values themselves are derived from formulas. Any help is much appreciated, thanks!

 

[Jonmo1]

I think you'll need an array formula, entered with CTRL + SHIFT + ENTER

=SUM(IF(F6:F9="butter",IF(E6:E9="low",IF(ISNUMBER($G$6:$M$9),$G$6:$M$9))))

 

[Jonmo1]

Question:

I can't put zeros in there because sometimes the actual value is a zero.

Why does that mean you can't put a zero there?
It will still have the same effect of not being summed..

 

[JoeJackson12]

Question:


Why does that mean you can't put a zero there?
It will still have the same effect of not being summed..

Because I/others will occasionally look at the actual table for information and a zero would be misleading.

 

[Jonmo1]

Fair enough.

The array formula should work for you then.

 

[FDibbins]

if the NA is comin g from a formula, why not use "" instead? Same affect as a 0 in this case, but it will look empty

 

[Jonmo1]

if the NA is comin g from a formula, why not use "" instead?

"" will also cause #Value ! result of the sumproduct.

 

[Marcelo Branco]

if the NA is comin g from a formula, why not use "" instead? Same affect as a 0 in this case, but it will look empty

An alternative is to use IFERROR making the formula return a text, N/A for example, rather than the error #N/A, and use
=SUMPRODUCT((N(+$G$6:$M$9))*(F6:F9="butter")*(E6:E9="low"))

M.

 

[FDibbins]

An alternative is to use IFERROR making the formula return a text, N/A for example, rather than the error #N/A, and use
=SUMPRODUCT((N(+$G$6:$M$9))*(F6:F9="butter")*(E6:E9="low"))

M.

Good catch Jonmo, and nice alternative Marcelo