## 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, click on "Find & Select" button and then click on "Go To.."

Click on "Special..." button.

Click on "Blanks" and then on "OK" button.

Go to tab "Home" on the ribbon and click 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.

### Download excel *.xlsx file

### Category: Sum function

Identify numbers in sum using solver

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]Comments(48) Filed in category: Combinations, Excel, Solver, SUM function

Sum values between two dates with criteria in excel

In this post, I will provide a formula to sum values in column (Qty) where an column (Date) meets two […]Comments(27) Filed in category: Excel, SUM function

Sum values in a range where adjacent cell value equals a criterion

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is […]Comments(19) Filed in category: Excel, SUM function

Comments(13) Filed in category: Excel, MMULT function, Permutations, SUM function

Filter unique distinct list sorted based on sum of adjacent values [Array Formula]

Table of Contents Filtering unique distinct text values and sort them based on sum of adjacent values Filtering unique distinct […]Comments(11) Filed in category: Excel, SUM function

### Category: Sumproduct

Running totals within date range

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]Comments(9) Filed in category: Excel, SUMPRODUCT function

How to use Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.Comments(2) Filed in category: Excel, SUMPRODUCT function

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

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.