## Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell?

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

### Instructions

Select all values and the blank total cells.

Press F5 or go to tab "Home" on the ribbon, press with left mouse button on "Find & Select" button and then press with left mouse button on "Go To.."

Press with mouse on "Special..." button.

Press with mouse on "Blanks" and then on "OK" button.

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

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

Select cell C18 and type this formula:

Press Enter. Copy cell C18 and paste to cell range D18:F18.

### Explaining formula in cell C18

ISFORMULA(C3:C17) 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.

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

The SUMPRODUCT function then sums all values in the array. Why not use the SUM function? You need to enter it as an array formula if you use the SUM function.

### Get excel *.xlsx file

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

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

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

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

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

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