Using SUMPRODUCT with missing data elements

=IFERROR(SUMPRODUCT(VLOOKUP(A147,FULLYEAR,{3,10},0)),0)


I have the above formula to capture and sum the data in two columns within a named range (FULLYEAR). It works great until one column is blank. How do I get the formula to treat blank cells as zeros so that I can capture the true total. Currently I am getting zero (because of the IFERROR portion of my statement). If I used ISNA....I'll get N/A. If I take the IF statement out alltogether, I get N/A again.

Help please.

Thank you!

I figured it out. Thanks!

Please remember to mark your thread as Solved and it would be beneficial to others finding this thread to post your solution.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

=IFERROR(VLOOKUP(A138,Quarter1,3,FALSE)+VLOOKUP(A138,Quarter2,3,FALSE),0)


Okay, maybe I didn't figure this thing out. I've changed my formula to what you see above.

Problem: The lookup value in column A may not appear in both named ranges. If it only appears in the 2nd quarter, I get 0 or N/A. So I changed my formula to the following;


=IFERROR(VLOOKUP(A138,Quarter1,3,FALSE)+VLOOKUP(A138,Quarter2,3,FALSE),VLOOKUP(A138,Quarter2,3,FALSE))

This will work fine, right up to the point the data only appears in Quarter 1. It still gives me zero or N/A. I'd like to write a line of code that will return the sum total of both quarters, even if there is nothing in one quarter and something in another without having to drop fake data into column A for the table to recognize.

---------- Post added at 12:58 PM ---------- Previous post was at 12:57 PM ----------

Cutter....I did mark it solved. Then I "unsolved it". But thanks for the tip.....now, maybe you can help me with my real problem. :-)

That worked! Thank you!