sumproduct
 

Hi I am having a problem with a table I have created

I am using multiple criteria I need to add up totals per person per item if you get what I am saying, The formula comes up with no errors, but it is not finding ding anything

=SUMPRODUCT(--(Data!Driver=Variances!A$3)*(Data!Fleet=Variances! B$2),Data!Variance)

Any thoughts?

Thanks

sumproduct
 

"montypython" wrote:
I am using multiple criteria I need to add up totals per person
per item if you get what I am saying, The formula comes up with
no errors, but it is not finding ding anything
=SUMPRODUCT(--(Data!Driver=Variances!A$3)*(Data!Fleet=Variances! B$2),Data!Variance)

The double-negative (--) is superfluous since you are multiplying (*)
conditions. However, that does not cause any problems.

The formula will return zero if either of the following conditions applies:


1. The corresponding values in Data!Variance are text "numbers" or empty.

What does the following formula return (it should return zero):

=SUMPRODUCT(--(ISNUMBER(Data!Variance)=FALSE))

You might try --Data!Variance. But that is just a work-around. It would be
better if the values in Data!Variance were numeric, if that is your intent.


2. There are no instances where both of the conditions are true.

Again, the problem might be mix of text "numbers" and numeric values.

What does the following formula return (it should return zero):

=(ISNUMBER(Variances!A$3)=FALSE)+(ISNUMBER(Varianc es!B$2)=FALSE)
+SUMPRODUCT(--(ISNUMBER(Data!Driver)=FALSE))
+SUMPRODUCT(--(ISNUMBER(Data!Fleet)=FALSE))

You might try writing --Data!Driver, --Data!Fleet, --Variances!A$3
and --Variances!B$2. But again, those are work-arounds, not real fixes.