Excel Formula to Add Two Cells When One May Be Blank
Question details
The user needs to calculate the total of two cells in a new column but requires a formula that will not return a FALSE error if one of the source cells happens to be blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating row totals from two data columns where incomplete records might leave one of the columns blank.
- Observed behavior
- Instead of failing or returning a FALSE error when encountering an empty cell, the calculation should correctly sum the available number or return the single value.
Verify that your blank cells are truly empty and do not contain hidden spaces or unprintable characters, as these can sometimes cause formula errors.
Use the SUM Function to Ignore Blank Cells
The standard SUM function is the most reliable way to add cells because it automatically ignores blank cells and text strings.
When using the simple addition operator (e.g., =J4+K4), a cell containing a hidden space or text might trigger a #VALUE! error. The SUM function naturally skips over these non-numeric values, making it perfect for columns with blanks.
Click on the cell in your total column (for example, cell L4) where you want the result to be displayed.
Type =SUM(J4:K4) into the formula bar and press Enter. If J4 is blank, the formula will simply output the value of K4.
Click the small square at the bottom-right corner of cell L4 and drag it down to apply this formula to the rest of the rows in your table.
Use IF and COUNTA for Completely Blank Rows
Use this combined formula if you want the total cell to appear completely blank (rather than showing a 0) when both of your source cells are empty.
Use WPS Spreadsheet to Handle Blank Cells Seamlessly
WPS Spreadsheet is a powerful, highly compatible tool that handles logical functions and sums just like Microsoft Excel. You can quickly add up rows with missing data using its intuitive interface.
- 1. Open your workbook in WPS: Launch WPS Spreadsheet and open the document containing your data.
- 2. Input the calculation: Click your total cell and type =SUM(J4:K4) to cleanly add the values.
- 3. Auto-fill the rest: Double-click or drag the fill handle in the corner of the cell to calculate the remaining rows instantly.
Frequently Asked Questions
Why does using the plus sign (+) return an error with blank cells?
Using the plus operator (e.g., =J4+K4) works properly if the blank cell is perfectly empty. However, if the seemingly blank cell contains a space character or is formatted as text, the plus operator will fail and return a #VALUE! error. The SUM function safely ignores these spaces and text.
How can I force blank cells to be treated as zero in complex formulas?
If you are using formulas where SUM is not applicable, you can wrap your cell references in the N() function. For example, =N(J4)+N(K4) will evaluate any blank cells or text strings directly as a zero.
How do I sum multiple non-adjacent columns while ignoring blanks?
You can use the SUM function and select individual non-adjacent cells separated by commas, such as =SUM(A2, C2, E2). SUM will automatically skip any blank cells among those specific selections.
