Sumproduct with criteria with null in the array

Lunacy4682

New Member
Joined
Jul 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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<>"")))

1594735135705.png

1594735292763.png
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,001
Office Version
  1. 365
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

New Member
Joined
Jul 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,001
Office Version
  1. 365
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,678
Messages
5,637,741
Members
416,981
Latest member
PLonchar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top