How to account for blank cells using the SumProduct formula

[Craig4670]

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!

 

[Jonmo1]

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]

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?

Thank you for your response.
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]

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.

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

 

[Craig4670]

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.

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

Thanks for your help! It worked!

 

[Jonmo1]

Glad to help, thanks for the feedback