Thread: Sumproduct returning zero

Not giving much away are you

VBA Noob

VBA Noob: Sorry for the brevity of the request. I sometimes get caught up in the problem and am either too brief or too long winded, but I do appreciate the help. I some times assume that since I know what I'm doing, everybody else does.

Daddy Long Legs: I went with the long sumproduct formula, because of the small size of the workbook in this project. I had written the long version several times, but had used "" instead of zero in the logical test (assuming they were the same) and should have known better. Thanks for the explanation about the number-error thing with sumproduct. Even though I read the sumproducts piece at xldynamics; I must've missed that.

So, one more question, for anyone out there, given my new formula:

Code:

=IF(SUMPRODUCT(--($A$8:$A$120=$A132),--($C$8:$C$120=$C132),--($G$8:$G$120)*$D132)=0,"",SUMPRODUCT(--($A$8:$A$120=$A132),--($C$8:$C$120=$C132),--($G$8:$G$120)*$D132))

A8:A120 is are the Employee Numbers matched against the Employee Code in A132.
C8:C120 are the Pay Rate Codes for those employees (each employee can have up to four distinct rates which is in C132.
G8:G120 (actually columns E to S) are the number of hours worked each day of the pay period multiplied by D132 which is the actual amount for that pay rate.

I can work with what I have, but my question is: Is there a more efficient way to accomplish this end. No rush here, I'm trying to pick your minds.

Thanks again,

Dean

Originally Posted by daddylonglegs

SUMPRODUCT always returns a number (if not an error).If you want a blank then the easiest way is to format the cell, something like

General;;

then zero will look like blank. Or you could make yourself a nice long formula with

=If(sumproduct_formula=0,"",sumproduct_formula)

when i searching the forum for my question, i thought the above may help me in solving the issue.

In the above said sumproduct formula results 0, if the result is 0 (here we are considering blank cell as 0), if it is true then blank, if not then sumproduct formula

but what if the sumproduct formula is really picks up "0" (as 0 is available in the source) instead of blank then how to handle can any one sort out this issue.
thanks in advance

hi there,
any one can throw some light on this and advice me the solution to sort out my query.

any speedy help will be appreciated.

Thanks in Advance.

As daddylonglegs said (way back when), SUMPRODUCT will only return a number or an error. It doesn't return a blank cell, and it will only return a 0 when the result is actually 0.

So if you use a SUMPRODUCT formula and you get a 0 result, it's either correct or your formula wasn't written correctly to get the result you were intending.

Thanks for the speedy help pjoaquin.

i understood the meaning of your reply.

any how my scenario is i need to lookup a value basing on two conditions from another sheet. i wrote a sumproduct formula and when the value is there then, the formula is getting the value (irrespective of 0 or 24 or 365 for example offcourse my data contains 0's as well). but if the cell is blank then the sumproduct formula is getting 0 instead of blank. i want to avoid this.

hope i am clear in asking
thanks in advance.

Any advice is really appreciable to my query
TIA