#VALUE error when using sumproduct (multiple criteria)

[pacer31]

Hello!
I need help figuring out why my sumproduct formula below returns a #VALUE error. Perhaps the syntax for my particular case is not correct as it took much experimenting to get a non-error yielding, correct result for similar formulas with 2 criteria (which worked with the syntax below) and 3 criteria (which returned correct negative values, easily resolved by multiplying -1. Or maybe it is a problem with my data types which include text, dates, and currency. I have tried switching the text cells to general but to no avail. Again, the 2 and 3 criteria formulas worked given the data types.

=SUMPRODUCT(-('Credit Card'!$B$3:$B$1700=Sheet1!A13),-('Credit Card'!$A$3:$A$1700=$B$2),-('Credit Card'!$E$3:$E$1700<=$B$9),-('Credit Card'!$E$3:$E$1700>=$B$8),'Credit Card'!$C$2:$C$1700)+SUMPRODUCT(-('Employee Reimbursment'!$B$3:$B$1701=Sheet1!A13),-('Employee Reimbursment'!$A$3:$A$1701=$B$2),-('Employee Reimbursment'!$F$3:$F$1700<=$B$9),-('Employee Reimbursment'!$F$3:$F$1700>=$B$8),'Employee Reimbursment'!$D$3:$D$1701)+SUMPRODUCT(-('Invc sheet'!$B$1:$B$1700=Sheet1!A13),-('Invc sheet'!$A$1:$A$1700=$B$2),-('Invc sheet'!$G$3:$G$1700<=$B$9),-('Invc sheet'!$G$3:$G$1700>=$B$8),'Invc sheet'!$D$1:$D$1700)

Please let me know if more information is needed.
I appreciate any help as I have been struggling with this for hours!

[DonkeyOte]

Not all of the arrays in your SUMPRODUCTS are of the same dimensions - ie

Sumproduct # 1 dimensions used: 3:1700 / 2:1700
Sumproduct # 2 dimensions used: 3:1701 / 3:1700
Sumproduct # 3 dimensions used: 1:1700 / 3:1700

I suspect the above inconsistencies arise from typos - so simply correct the ranges such that they are consistent (at least on an individual SUMPRODUCT basis)

on an aside - there's really no value in using single unary operator when compared to double unary - the overhead of the 2nd unary is minimal
using single you must be aware of the count of boolean tests at all times so as to ensure correct sign is returned - ie unnecessary complication / risk

[JBeaucaire]

Cross post link:
http://www.mrexcel.com/forum/showthread.php?t=465575

Pacer, this forum is an awesome resource, but be sure to read through the Forum Rules so you can use and follow them effectively. For instance, rule #8 concerns cross-posting...you must include links to the other threads where the same question is posted. The last thing you want to do is waste people's time with a question that may have already been resolved elsewhere, no?

[pacer31]

Not all of the arrays in your SUMPRODUCTS are of the same dimensions - ie

Sumproduct # 1 dimensions used: 3:1700 / 2:1700
Sumproduct # 2 dimensions used: 3:1701 / 3:1700
Sumproduct # 3 dimensions used: 1:1700 / 3:1700

I suspect the above inconsistencies arise from typos - so simply correct the ranges such that they are consistent (at least on an individual SUMPRODUCT basis)

on an aside - there's really no value in using single unary operator when compared to double unary - the overhead of the 2nd unary is minimal
using single you must be aware of the count of boolean tests at all times so as to ensure correct sign is returned - ie unnecessary complication / risk

Yes yes thank you very much! I should have caught those inconsistencies.. As for the boolean tests, I guess I was playing around before and could only get it to work with them sing unaries as I was unaware of odd vs. even sign changes regarding sumproduct; I should know enough of how my sumproducts are working to clean those up now. Thanks again!

Cross post link:
http://www.mrexcel.com/forum/showthread.php?t=465575

Pacer, this forum is an awesome resource, but be sure to read through the Forum Rules so you can use and follow them effectively. For instance, rule #8 concerns cross-posting...you must include links to the other threads where the same question is posted. The last thing you want to do is waste people's time with a question that may have already been resolved elsewhere, no?

My apologies, usually I can figure what etiquette is on forums but I'd never considered any custom on asking the same thing on different forums. It's so obvious to me now the benefit!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[pacer31]

Just one more thing, please .. I'm not sure if I should have started a new thread for this issue since it seems pretty mudane: Is there a way to format the cells so that excel treats extra "space" keystrokes at the end (and beginning?) of general/text cells the same as the intended perfect entry? (since these problem cells are being overlooked in my formulas)

[DonkeyOte]

See TRIM function... ie encase the appropriate range reference (that which contains the strings in question) within a TRIM call prior to evaluating it...

=SUMPRODUCT(--(TRIM(range)="criteriastring"),--(range2=criteria2),rangetosum)

[pacer31]

thanks guys!