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