• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT ignore blanks in sum range

A

Anon9149

New Member
Evening all,

With respect to the attached example.

I have a range that is all formula based and some of the results of those formula are blanks. I need to use that range in a sumproduct function, but it's returning '#VALUE!' errors.

Can somebody assist? With reference to the second attempt in the sheet, the formula successfully counts the number of cells that meet the criteria. It just falls down when it comes to asking the formula to sum those cells (third attempt).

Thanks in advance!
 

Attachments

  • Chandoo 6.xlsx
    12.3 KB · Views: 13
Your issue is that the cells C5:I8 and C13:I14 contain blank text strings. If you wish them to not contain anything then delete them. If you wish for them to contain strings then you need to change the sum area. Once I deleted the empty string values, they calculated.
 

Attachments

  • Chandoo 6.xlsx
    12.2 KB · Views: 7
AlanSidman said:
Your issue is that the cells C5:I8 and C13:I14 contain blank text strings. If you wish them to not contain anything then delete them. If you wish for them to contain strings then you need to change the sum area. Once I deleted the empty string values, they calculated.
Click to expand...

Thanks Alan for taking the time to look at this for me. Much appreciated!

Yes correct, they contain empty text strings. When creating a sample for the purpose of this query, I copied and condensed my actual sheet, but left these in as these represent formulas that return blank values in the actual document. I think I specified that in the sheet. So I need a solution that is capable of summing a range that has non-empty cells containing blank values.
 
Suggest you upload a realistic file for testing. No one here wants to recreate a file for you. Help us to help you. We are all volunteers with limited time for this forum.
 
bosco_yip said:
Hi,

Using a comma "," instead of Multiply "*" can solve the SUMPRODUCT blank cells problem. Please see,
Click to expand...

Bosco, thank you very much for providing a kind, politely worded and effective solution! It works excellently! I can't thank you enough, you've helped me hit a deadline I didn't think I would reach!
 
Last edited by a moderator:
You must log in or register to reply here.
Back
Top