Ignoring Blank cells in a formula to avoid #value! error

[volensaeros]

=IF(U5="","",(O5*V5)+(P5*W5)+(Q5*X5)+(R5*Y5)+(S5*Z5)+(T5*AA5))

Some of these product produce a blank return which is giving me the #VALUE!

how do i remedy this

 

[Joe4]

Welcome to the Board!

Which of these cells have this (possibly all or just certain cells)?
Are they really blank (empty cells), or are there spaces in there?
Are these cells the results of formulas or hard-coded?
If formulas, what do the formulas in those cells look like (it is usually much easier to address the situation in those underlying formulas)?

 

[volensaeros]

Cells O4:S4 are manual inputs with no spaces in them

V4:Z4 have a Formula:
=IF(ISERROR(VLOOKUP($B4,Table,COLUMN(H$197),FALSE)),"",(VLOOKUP($B4,Table,COLUMN(H$197),FALSE)))

U5 is a manual input black cell

heres the sheet:
https://drive.google.com/open?id=0B7_jHAY7wlceZ3FNZVptT2tEeE0&authuser=0

I'm working on the first line (4) with the objective to eliminate any data being shown if there are no inputs

 

[Jonmo1]

What would you like to happen when any of those cells are blank
FYI, they're not really blank, they're "" results from your formula in V4:Z4
"" is a TEXT string.


This will essentially treat those ""'s as 0's.

=IF(U5="","",SUMPRODUCT(O5:T5,V5:AA5))

 

[volensaeros]

sheet "vending income"

 

[Joe4]

Is there any issue with having your underlying formulas return 0 instead of "", i.e.

[COLOR=#333333][FONT=Lucida Grande]=IF(ISERROR[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande]VLOOKUP[/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]$B4[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]Table[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],COLUMN[/FONT][/COLOR][COLOR=#A54A29][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande]H$197[/FONT][/COLOR][COLOR=#A54A29][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],FALSE[/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],0,[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande]VLOOKUP[/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]$B4[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]Table[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],COLUMN[/FONT][/COLOR][COLOR=#A54A29][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande]H$197[/FONT][/COLOR][COLOR=#A54A29][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],FALSE[/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande])[/FONT][/COLOR]

Then you won't get those errors.

By the way, if you are using Excel 2007 or later, you can reduce that formula down to:

[COLOR=#333333][FONT=Lucida Grande]=IFERROR[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande]VLOOKUP[/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]$B4[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]Table[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],COLUMN[/FONT][/COLOR][COLOR=#A54A29][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande]H$197[/FONT][/COLOR][COLOR=#A54A29][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],FALSE[/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#333333][FONT=Lucida Grande],0)[/FONT][/COLOR]

 

[volensaeros]

genius!