# SUMPRODUCT N/A question

#### thewiseguy

##### Well-known Member
In sheet 3, I have a SUMPRODUCT formula which is returning a value of N/A. I am trying to figure out why. The only reason I can think of is b/c the formula is looking back at Sheet 2, Cell A3 in which A3 has a formula like: =C2.

Is this why my SUMPRODUCT formaula is returning N/A??

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
can you post the actual formula please

Ditto what Dave said.

Do any of the source ranges hold #N/A values?

Sheet 3 (formula which is returning N/A)

=SUMPRODUCT(--(Energy!\$B\$5:\$B\$285=B15),--(Energy!\$CC\$5:\$CC\$285=K15),Energy!\$CE\$5:\$CE\$285)

Energy Sheet

CE5 = "=AT5"

My guess is that bc CE5 equals a refernce to another cell, I am getting an N/A

My bet would be you have an error value (#N/A) in either Energy!\$B\$5:\$B\$285 or Energy!\$CC\$5:\$CC\$285 or Energy!\$CE\$5:\$CE\$285...

My bet would be you have an error value (#N/A) in either Energy!\$B\$5:\$B\$285 or Energy!\$CC\$5:\$CC\$285 or Energy!\$CE\$5:\$CE\$285...

you are right! How would I tweak this formula to get rid of the N/A?

=VLOOKUP(AQ5,ORDERINFO,35,FALSE)

Hi There

=IF(ISNA(VLOOKUP(AQ5,ORDERINFO,35,FALSE)), "", VLOOKUP(AQ5,ORDERINFO,35,FALSE))

You can change the "" to a value of 0 if you prefer

Hi There

=IF(ISNA(VLOOKUP(AQ5,ORDERINFO,35,FALSE)), "", VLOOKUP(AQ5,ORDERINFO,35,FALSE))

You can change the "" to a value of 0 if you prefer

Thanks Dave!

Replies
8
Views
274
Replies
13
Views
835
Replies
3
Views
115
Replies
11
Views
579
Replies
2
Views
318

1,214,523
Messages
6,120,031
Members
448,940
Latest member
mdusw

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

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