# Sumproduct with criteria with null in the array

#### Lunacy4682

##### New Member
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<>"")))

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Oaktree

##### MrExcel MVP

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

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
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.

Replies
6
Views
274
Replies
10
Views
581
Replies
1
Views
331
Replies
1
Views
177
Replies
0
Views
44

1,127,198
Messages
5,623,312
Members
415,966
Latest member
ctorohuamanchumo

### 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.

### Which adblocker are you using?

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

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