Sumproduct with criteria with null in the array

[Lunacy4682]

Hello Team.
I have been using this forum to learn and find my dumb mistakes for years. Thanks for all your posts and my education.

Here is a problem that I can seem to figure out. Any help is MUCH appreciated.

I am using a Sumproduct to total up an array from a few lists :

-Week Summary tab has this =(SUMPRODUCT((IMPORT!$L$2:$L$10000=$C$3)*(IMPORT!$D$2:$D$10000=$N10)*(IMPORT!$H$2:$H$10000=O$8)*IMPORT!$J$2:$K$10000))

-Import Tab has this in the J & K columns =(IF(B3="","",(IF((E3*(VLOOKUP(B3,Key!$C$2:$G$200,4,FALSE)))=0,"",(E3*(VLOOKUP(B3,Key!$C$2:$G$200,4,FALSE)))))))

I know that if I remove the null value option from the import tab - (IF(B3="","", - that the formula works. I have also tried --(IMPORT!$J$2:$K$10000<>"") as shown below. I can't seem to get the sumproduct to ignore the null value and I know I'm missing something simple...

Here is what I have tried. =(SUMPRODUCT((IMPORT!$L$2:$L$10000=$C$3)*(IMPORT!$D$2:$D$10000=$N10)*(IMPORT!$H$2:$H$10000=O$8)*IMPORT!$J$2:$K$10000,--(IMPORT!$J$2:$K$10000<>"")))

 

[Oaktree]

The short answer is to use 0 instead of "" in your formulas for J&K.

Because you're using the * operator, your SUMPRODUCT formula is trying to multiply by the text "" value and that's causing the #VALUE! error. If you have 0s instead of "" values, that will be fixed.

One other thing to note: the --(IMPORT!$J$2:$K$10000<>"") effectively does nothing because your #VALUE error comes before that part of the formula. In other words, you'd be multiplying #VALUE! from the part before the comma by the 1's & 0's from --(IMPORT!$J$2:$K$10000) and you'll still get a #VALUE! error.

 

[Lunacy4682]

The short answer is to use 0 instead of "" in your formulas for J&K.

Because you're using the * operator, your SUMPRODUCT formula is trying to multiply by the text "" value and that's causing the #VALUE! error. If you have 0s instead of "" values, that will be fixed.

One other thing to note: the --(IMPORT!$J$2:$K$10000<>"") effectively does nothing because your #VALUE error comes before that part of the formula. In other words, you'd be multiplying #VALUE! from the part before the comma by the 1's & 0's from --(IMPORT!$J$2:$K$10000) and you'll still get a #VALUE! error.

Thanks for the feedback. I was trying to avoid the 0 instead of "" as it makes everything look busy and as a matter of contention with myself.

Also, thanks for the comment on --(IMPORT!$J$2:$K$10000<>""). I was just plugging away and didn't even think about the actual math that was going on. (yea, I'm that dense sometimes)

 

[Oaktree]

Just because the value is 0 doesn't mean you need to see it!

Using a custom number format of "#,##0;(#,##0);" (without the quotes) would hide the zeroes entirely or "#,##0;(#,##0);-" would show zeroes as just a hyphen... in other words, after the second semicolon is how you want 0s to appear.