# Find empty cells and sum cells above

This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and returns a total.

Is it possible to quickly select all empty cells and then sum cells above to the next empty cell? Yes, I will show you how.

Can I have a formula in grand total (row 18) that only sums all the totals above? Yes!

**What's on this page**

- How to select empty cells in a cell range
- Populate empty cells with a formula
- Add grand totals that only sums cells populated with formulas - Excel 2013
- Add grand totals that only sums cells populated with formulas - SUMIF function
- Add grand totals that only sums cells populated with formulas - Excel 365
- Get Excel *.xlsx file

## 1. How to select empty cells in a cell range

The image above demonstrates how to find empty cells in a given cell range.

- Select all values and the blank total cells.

- Press F5, a dialog box appears.

- Press the left mouse on the "Special..." button.

- Press on "Blanks" to select it, then press on the "OK" button.

## 2. Populate empty cells with a formula

- Go to tab "Home" on the ribbon and press with left mouse button on the "AutoSum" button.

- All empty cells now have a SUM formula that adds all the above values to the next SUM formula.

## 3. Add grand totals that only sums cells populated with formulas

- Select cell C18 and type this formula:
=SUMPRODUCT(ISFORMULA(C3:C17)*C3:C17)
- Press Enter. Copy cell C18 and paste to cell range D18:F18.

### 3.1 Explaining formula in cell C18

#### Step 1 - Check if cell contains a formula

The ISFORMULA function checks if a cell in cell range C3:C17 has a formula. It returns TRUE or FALSE.

ISFORMULA(C3:C17)

returns this array:

{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}

The picture below shows this array in column D.

The array shows that there is a formula in C5, C9, C12, and C17.

#### Step 2 - Multiply with value

The asterisk character allows you to multiply numbers and boolean values.

ISFORMULA(C3:C17)*C3:C17 multiplies the boolean values with their corresponding values in column C, shown in column E below.

ISFORMULA(C3:C17)*C3:C17

becomes

{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}*C3:C17

becomes

{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}*{748; 508; 1256; 283; 960; 23; 1266; 821; 658; 1479; 970; 109; 599; 252; 1930}

and returns

{0; 0; 1256; 0; 0; 0; 1266; 0; 0; 1479; 0; 0; 0; 0; 1930}

#### Step 3 - Add values and return the total

The SUMPRODUCT function then sums all values in the array.

SUMPRODUCT(ISFORMULA(C3:C17)*C3:C17)

becomes

SUMPRODUCT({0; 0; 1256; 0; 0; 0; 1266; 0; 0; 1479; 0; 0; 0; 0; 1930})

and returns 5931 in cell C18.

Why not use the SUM function? You need to enter it as an array formula if you use the SUM function. Use the SUM function if you are an Excel 365 user.

## 4. Add grand totals that only sums cells populated with formulas - Excel 2013

The following formula won't work, the SUMIF function seems to not be capable of processing the ISFORMULA function. The ISFORMULA function is an Excel 2013 function, they seem incompatible.

=SUMIF(C3:C17, ISFORMULA(C3:C17))

Let me know if you have a solution that allows me to use the SUMIF function.

## 5. Add grand totals that only sums cells populated with formulas - Excel 365

=SUM(FILTER(C3:C17, ISFORMULA(C3:C17)))

### 5.1 Explaining formula

#### Step 1 - Check if cell contains formula

The ISFORMULA function returns a boolean value TRUE or FALSE if a cell contains a formula or not.

ISFORMULA(C3:C17)

#### Step 2 - Filter numbers based on boolean values

FILTER(C3:C17,ISFORMULA(C3:C17))

#### Step 3 - Add numbers and return a total

SUM(FILTER(C3:C17,ISFORMULA(C3:C17)))

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]

To extract groups from cell range B3:B10 I use the following regular formula in cell B13. Related articles VLOOKUP and return […]

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]

The easiest way to sum a cell range is to simply select the cell range and read the values in […]

This article explains why your formula is not working properly, there are usually four different things that can go wrong. […]

### 4 Responses to “Find empty cells and sum cells above”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

As alternative solution in cell C18 you can write the formula

`=SUM(C3:C17)/2`

miho66,

Yes, you are right. A lot easier, thanks for commenting.

Another complete this task:

after use autosum, select again whole range (C3:F17) and use Find&Replace to replace sting "sum(" with "subtotal(9,"

Ciprian Stoian,

Yes, you are right. The SUBTOTAL function ignores other SUBTOTALS to avoid double counting.

Thank you for commenting.