Referencing Lookup cells

[Goldy13]

I have 3 spreadsheets:
- Sheet A, with two columns (Date & Product Count)
- Sheet B, the reference sheet for Sheet A
- Sheet C, the sheet that summarizes Sheet A

The cell in Sheet B has this formula: =(LOOKUP(B2107,$BB$6:$BB$17,$BC$6:$BC$17))*V2107. This formula results in an #N/A because cell B2107 is blank. The cell in Sheet A equals the cells in Sheet B, so this also gives #N/A.

The 3rd spreadsheet sums up data from sheet A. A cell there has this formula: =SUMPRODUCT(--('Data Sheet'!$A$6:$A$6470>=DATE(2007,1,1)),--('Data Sheet'!$A$6:$A$6470<=DATE(2007,1,31)),'Data Sheet'!C$6:C$6470)

Since I am getting the #N/A result in Sheet A, it will not sum the results that I need and also gives a #N/A result. How can I fix either formula to not give a #N/A result? Should I change the lookup function in Sheet B to give a zero if the reference cell (B2107) is blank? Or can I make a function in Sheet A to say that if the reference cell gives an error display a 0? Thanks for any feedback.

[NBVC]

You can convert the Lookup formula to return 0 when B2107 is blank

=IF(B2107="",0,(LOOKUP(B2107,$BB$6:$BB$17,$BC$6:$BC$17))*V2107)

or

you can change formula in Sheet B to something like:

=if(ISNA(SheetB!B2107),0,SheetB!B2107)