Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct instead of SumifS in VBA (application.sumproduct) | Excel Programming | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct mod row | Excel Worksheet Functions |