## SUMPRODUCT if not blank

*Article last updated on February 05, 2018*

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

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

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

Create a unique distinct sorted list containing both numbers text removing blanks

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]

Extract a unique distinct list sorted alphabetically and ignore blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

Sorting numbers and text cells in an descending order also removing blanks

This blog article is one out of five articles on the same subject. Sorting text cells using array formula in […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

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

Dynamic team generator in excel

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form