# 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)))

Sum values between two dates and based on a condition

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

Running totals based on criteria

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

The formula in cell D3 adds all unique numbers in cell range B3:B12 and returns the total. Unique values are all […]

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 […]

Sum numerical ranges between two numbers

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. Weekly Blog EMAIL Email Welcome! […]

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

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