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 category
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
Excel categories
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.
As alternative solution in cell C18 you can write the formula
=SUM(C3:C17)/2miho66,
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.