SUMPRODUCT if not blank
The above image demonstrates how to ignore blank cells in a SUMPRODUCT formula. The following formula is shown in cell E3.
There is no need for an IF function, simply use the ISBLANK function and then multiply with the corresponding cell range.
The ISBLANK function returns TRUE or FALSE determined by if cell is blank or not. Since we are using a cell range the ISBLANK function returns an array with the same size as the cell range.
The NOT function comes in handy when you want to convert the boolean values to their opposites. For example, TRUE becomes FALSE and FALSE becomes TRUE.
becomes
The next step is to multiply the boolean array with cell range C3:C7.
becomes
and returns
The SUMPRODUCT function then adds all numerical values in the array returning 6 in cell E3.
1+2+0+2+1 = 6
Formula returns blank
Cell range B3:B7 contains a formula that sometimes returns a character and sometimes a blank. The ISBLANK function won't work in this case, see cell B14, it returns 0 which is incorrect.
We need to rely on larger than and smaller than characters <>, see formula in cell B10.
Together like this <> means not equal to. Two double quotes "" is nothing.
B3:B7<>"" is a logical expression and returns an array of boolean values with as many values as the number of cells in the cell range B3:B7.
The parentheses determine the order of calculations, we need it to compare the cell range with nothing before multiplying with the cell range.
becomes
becomes
and returns
The SUMPRODUCT function sums all values in the array.
and returns 10 in cell B10. 1+2+0+3+4 = 10.
Download excel *.xlsx file
Merge two columns with possible blank cells
Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]
Extract unique distinct values A to Z from a range and ignore blanks
This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]
How to use the ISBLANK function
The ISBLANK function returns TRUE if the argument is an empty cell, returns FALSE if not. Excel Function Syntax ISBLANK(value) […]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
How to do tiered calculations in one formula
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]
SUMPRODUCT – multiple criteria
The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to […]
How to use the SUMPRODUCT function
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
SUMPRODUCT and nested IF functions
I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how […]
You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]
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.