# How to account for blank cells using the SumProduct formula

#### Craig4670

##### Board Regular
Hello:

I have a question about using the SumProduct formula. I have a spreadsheet that is ongoing by the user. Some cells are blank and will be filled in at a later date. I have a formula that is working correctly - however I cannot figure out how to have the calculation account for the blank cells. The formula is based on monthly date ranges.

The formula I have is:
=SUMPRODUCT((B19:B24>="1/1/2013"+0)*(B19:B24<="1/31/2013"+0)*J19:J24)

Columns B19-B24 are the dates data was entered. I would like to expand the formula to B:19:B57
Columns J19-J24 is the data that I would like the formula to return. I would like to expand the formula to J19:J57

By expanding the cell ranges, the formula does not work because of the blank cells.

How do I use the ISBlank function in the above formula?

Any help would be appreciated.

Thanks!

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Jonmo1

##### MrExcel MVP
It works for me with blanks in either column B or J.

In what way does it not work for you?
do you get an error? what error?
do get the wrong results? what results DID you get? what results did you expect and why?

#### Craig4670

##### Board Regular
It works for me with blanks in either column B or J.

In what way does it not work for you?
do you get an error? what error?
do get the wrong results? what results DID you get? what results did you expect and why?

The formula works only if there is data in cells B19:B24 and J19:J24. The sheet has 57 rows. I wanted to change the formula to account for the 57 rows. When I change the number to 57, the formula does not account for the blanks. I get the value message. Once I change the formula back to 24 rows, it works correctly.

#### Jonmo1

##### MrExcel MVP
Are the values in column J the result of a formula, and your "blanks" are actually a formula that returns "" ?

Formulas returning "" are NOT Blank. They are a null text string.

=SUMPRODUCT(--(B19:B57>="1/1/2013"+0),--(B19:B57<="1/31/2013"+0),J19:J57)

#### Craig4670

##### Board Regular
Are the values in column J the result of a formula, and your "blanks" are actually a formula that returns "" ?

Formulas returning "" are NOT Blank. They are a null text string.

=SUMPRODUCT(--(B19:B57>="1/1/2013"+0),--(B19:B57<="1/31/2013"+0),J19:J57)

Thanks for your help! It worked!

#### Jonmo1

##### MrExcel MVP
Glad to help, thanks for the feedback

Replies
12
Views
221
Replies
2
Views
128
Replies
7
Views
153
Replies
4
Views
131
Replies
14
Views
730

1,195,905
Messages
6,012,222
Members
441,683
Latest member
XLGeezer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back