Need Assistance with #VALUE Error

[Dave52]

I have an issue with MS Home 2019 Excel.
I've been using this spreadsheet (and formulas) for years.

I hope this makes sense and my explanation is clear.

Rows B C D E F

All rows are numbers
D sums B & C.
E is another number.
F sums D & E.
The formulas in D & F are (basically) the same, Ex for Column F is : =IF(AND(ISBLANK(D104),ISBLANK(E104)),"",SUM(D104+E104))

My spreadsheet has 150 rows.
The first 103 rows/columns work as expected, empty cells until numbers are in B, C, D & E
Row 104 cells are empty
Suddenly at row 104 (and to 150) F has a #VALUE error.
When I "Review Formula" the evaluation keeps underlining a different area, usually underlining a "0", "N/A" or "blank".
I've checked for cell format, locked cells, etc. I can't find anything different in the remaining 104-150 cells.

TIA.

 

[Phuoc]

Try this:

=IF(D104&E104="","",SUM(D104,E104))

 

[StephenCrump]

Welcome to the Forum!

This just suggests that D104 and/or E104 aren't really blank cells.

Possibilities:
- The cell contains space characters (or other characters that appear to be blank but aren't) - you can use the LEN() function to test the character length of the cell.
- You have formulae returning a null string "". If that's the case, you'll need to test that the cell value <> "", as ISBLANK() will return False if there is a formula in the cell.

 

[Dave52]

Thank You for the replies.
I looked to see when I started using the spreadsheet as-is, 2010. I admit I didn't really understand the ISBLANK, heck most of the formula, but it worked so I just left it alone.
Phuoc, your suggestion works, Thanks.

Stephen, I ran LEN() on all B, C, D, E, rows 103 to 150, all came back 0.
When I ran Evaluate before, I did False as well as 0, BLANK and N/A.
At this point, I don't have an answer as to what the problem is/was, but I have learned to be glad something is fixed and forget about it.

Thanks Gentlemen,
Dave

 

[bebo021999]

If there is "" in either 2 cells, E104 or F104, ("" comes from a formula, or type:="" into cell)

Does not work (cell + cell):
E104+F104
SUM(E104+F104)

But, work (sum range, or sum single cell):
SUM(E104:F104)
SUM(E104,F104)

 

[Fluff]

But, work (sum range, or sum single cell):
SUM(E104:F104)
SUM(E104,F104)

But both of those will return 0 if they are blank & not ""