SUMPRODUCT N/A question

[thewiseguy]

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

 

[dave3009]

can you post the actual formula please

 

[Richard Schollar]

Ditto what Dave said.

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

 

[thewiseguy]

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

 

[Richard Schollar]

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

 

[thewiseguy]

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)

 

[dave3009]

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

 

[thewiseguy]

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!