• 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 ignoring blank cells

R

Rodrigues

Member
Hi There
I'm using a sumproduct function on file attached, does work fine apart from is displaying zeros which I believe it's because found empty cells. tried to add <>"" (on Sheet2 cell I4) without result.
Could someone shed any light on this?
many thanks in anticipation.
Regards
R
 

Attachments

  • Book2.xlsm
    10.2 KB · Views: 9
Hi AliGW
Nothing if no match.
I would like to stop displaying zeros, as this is part of a big file and at the moment I have loads of cells showing 0.
Thanks for your help and reply.
R
 
OK - try this:

=IF(SUMPRODUCT(--(Sheet1!$E$6:$E$8=Sheet2!H3),--(Sheet1!$I$6:$I$8=Sheet2!$I$2))=0,"",SUMPRODUCT(--(Sheet1!$E$6:$E$8=Sheet2!H3),--(Sheet1!$I$6:$I$8=Sheet2!$I$2)))

Does not need to be entered as an array formula, as far as I can see, so just ENTER (not CTRL+SHIFT+ENTER).
 
AliGW said:
OK - try this:

=IF(SUMPRODUCT(--(Sheet1!$E$6:$E$8=Sheet2!H3),--(Sheet1!$I$6:$I$8=Sheet2!$I$2))=0,"",SUMPRODUCT(--(Sheet1!$E$6:$E$8=Sheet2!H3),--(Sheet1!$I$6:$I$8=Sheet2!$I$2)))

Does not need to be entered as an array formula, as far as I can see, so just ENTER (not CTRL+SHIFT+ENTER).
Click to expand...

Just a bit adjusted to Ali's formula of the "$" portion :

=IF(SUMPRODUCT(--(Sheet1!$E$6:$E$8=Sheet2!H3),--(Sheet1!$I$6:$I$8=Sheet2!$I$2))=0,"",SUMPRODUCT(--(Sheet1!$E$6:$E$8=Sheet2!H3),--(Sheet1!$I$6:$I$8=Sheet2!$I$2)))

should read as :

=IF(SUMPRODUCT(--(Sheet1!$E$6:$E$8=Sheet2!$H3),--(Sheet1!$I$6:$I$8=Sheet2!I$2))=0,"",SUMPRODUCT(--(Sheet1!$E$6:$E$8=Sheet2!$H3),--(Sheet1!$I$6:$I$8=Sheet2!I$2)))

then, the formula can copy across and down

Regards
Bosco
 
You must log in or register to reply here.
Back
Top