|
Sumproduct formula not working
The following formula returns 0.
=SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)=610 06),Requisitions!$F$7:$F$1015) C F G H 6/29/2009 $29,466.00 41-70-80801-61006 80801 6/29/2009 $2,080.00 41-70-80801-61006 80806 6/29/2009 $8,840.00 41-70-80801-61006 80801 6/30/2009 $1,061.16 41-70-80801-61006 80804 7/1/2009 $4,433.90 41-70-80801-61006 80801 7/6/2009 $20,000.00 41-70-80801-61006 80801 The following works well with the 3rd variable of column (G) not being used =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),Requisit ions!$F$7:$F$1015) Your help is appreciated. |
Sumproduct formula not working
"Vince" wrote:
The following formula returns 0. [....] The following works well with the 3rd variable of column (G) not being used You need quotes around "61006" in the 3rd argument testing column G. Also, you are missing an paramenter in the RIGHT function in that 3rd argument; but I presume that is merely a typo in the posting. Anyway, it should be (correcting another syntax error): --(RIGHT(Requisitions!$G$7:$G$1015,5)="61006") Note: In the future, it is best to copy-and-paste formulas into postings, rather than retype them, especially when the question is about syntax or why the elements of a formula do not work as intended. GIGO. ----- original message ----- "Vince" wrote in message ... The following formula returns 0. =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)=610 06),Requisitions!$F$7:$F$1015) C F G H 6/29/2009 $29,466.00 41-70-80801-61006 80801 6/29/2009 $2,080.00 41-70-80801-61006 80806 6/29/2009 $8,840.00 41-70-80801-61006 80801 6/30/2009 $1,061.16 41-70-80801-61006 80804 7/1/2009 $4,433.90 41-70-80801-61006 80801 7/6/2009 $20,000.00 41-70-80801-61006 80801 The following works well with the 3rd variable of column (G) not being used =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),Requisit ions!$F$7:$F$1015) Your help is appreciated. |
Sumproduct formula not working
Vince,
It doesn't look like you have a "Lenght" argument for your RIGHT() function for column G... --(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)=610 06) ....should be... --(RIGHT(Requisitions!$G$7:Requisitions!$G$1015,5)=6 1006) ....although, I'm not sure if you can take the 5 right characters of a range of cells and compare them to a specific value. Let me know if it works out. OH!!! I just noticed something else. I'm assuming column G is text...well the RIGHT() function returns text anywas. You are trying to compare text to a numeric value. I'm not sure if that will work either. So...your original portion of the formula for column G... --(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)=610 06) ....might have to look like this... --(RIGHT(Requisitions!$G$7:Requisitions!$G$1015,5)=" 61006") HTH, Conan Kelly "Vince" wrote in message ... The following formula returns 0. =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)=610 06),Requisitions!$F$7:$F$1015) C F G H 6/29/2009 $29,466.00 41-70-80801-61006 80801 6/29/2009 $2,080.00 41-70-80801-61006 80806 6/29/2009 $8,840.00 41-70-80801-61006 80801 6/30/2009 $1,061.16 41-70-80801-61006 80804 7/1/2009 $4,433.90 41-70-80801-61006 80801 7/6/2009 $20,000.00 41-70-80801-61006 80801 The following works well with the 3rd variable of column (G) not being used =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),Requisit ions!$F$7:$F$1015) Your help is appreciated. |
Sumproduct formula not working
Try:
=SUMPRODUCT(--(YEAR(Requisitions!$C$7:$C$1015)=2009), --(Requisitions!$H$7:$H$1015=RIGHT(A3,5)), --(RIGHT(Requisitions!$G$7:$G$1015)="61006"), Requisitions!$F$7:$F$1015) This addressing syntax is pretty non-standard: Requisitions!$C$7:Requisitions!$C$1015 this is sufficient: Requisitions!$C$7:$C$1015 And =right() returns text. So the zipcode(???) has to be enclosed in quotes. Vince wrote: The following formula returns 0. =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)=610 06),Requisitions!$F$7:$F$1015) C F G H 6/29/2009 $29,466.00 41-70-80801-61006 80801 6/29/2009 $2,080.00 41-70-80801-61006 80806 6/29/2009 $8,840.00 41-70-80801-61006 80801 6/30/2009 $1,061.16 41-70-80801-61006 80804 7/1/2009 $4,433.90 41-70-80801-61006 80801 7/6/2009 $20,000.00 41-70-80801-61006 80801 The following works well with the 3rd variable of column (G) not being used =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),Requisit ions!$F$7:$F$1015) Your help is appreciated. -- Dave Peterson |
Sumproduct formula not working
the RIGHT function returns a text string, and you are then comparing it to a
number. One option is to place the 61006 within quotes (thus treating it like text), like so: =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)="61 006"),Requisitions!$F$7:$F$1015) the other option is to place the RIGHT function within a VALUE function, if you would rather compare numbers. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Vince" wrote: The following formula returns 0. =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)=610 06),Requisitions!$F$7:$F$1015) C F G H 6/29/2009 $29,466.00 41-70-80801-61006 80801 6/29/2009 $2,080.00 41-70-80801-61006 80806 6/29/2009 $8,840.00 41-70-80801-61006 80801 6/30/2009 $1,061.16 41-70-80801-61006 80804 7/1/2009 $4,433.90 41-70-80801-61006 80801 7/6/2009 $20,000.00 41-70-80801-61006 80801 The following works well with the 3rd variable of column (G) not being used =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),Requisit ions!$F$7:$F$1015) Your help is appreciated. |
Sumproduct formula not working
ps. Add that ,5 to the right() expression that I didn't see!
Vince wrote: The following formula returns 0. =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)=610 06),Requisitions!$F$7:$F$1015) C F G H 6/29/2009 $29,466.00 41-70-80801-61006 80801 6/29/2009 $2,080.00 41-70-80801-61006 80806 6/29/2009 $8,840.00 41-70-80801-61006 80801 6/30/2009 $1,061.16 41-70-80801-61006 80804 7/1/2009 $4,433.90 41-70-80801-61006 80801 7/6/2009 $20,000.00 41-70-80801-61006 80801 The following works well with the 3rd variable of column (G) not being used =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),Requisit ions!$F$7:$F$1015) Your help is appreciated. -- Dave Peterson |
Sumproduct formula not working
JoeU2004 - Thanks for the help. It was the quotes and the argument was
missing! Can't beleive I did not see that...looked at formula way to long I guess (forest for the trees!). I did copy and paste into the post by the way.. "JoeU2004" wrote: "Vince" wrote: The following formula returns 0. [....] The following works well with the 3rd variable of column (G) not being used You need quotes around "61006" in the 3rd argument testing column G. Also, you are missing an paramenter in the RIGHT function in that 3rd argument; but I presume that is merely a typo in the posting. Anyway, it should be (correcting another syntax error): --(RIGHT(Requisitions!$G$7:$G$1015,5)="61006") Note: In the future, it is best to copy-and-paste formulas into postings, rather than retype them, especially when the question is about syntax or why the elements of a formula do not work as intended. GIGO. ----- original message ----- "Vince" wrote in message ... The following formula returns 0. =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015)=610 06),Requisitions!$F$7:$F$1015) C F G H 6/29/2009 $29,466.00 41-70-80801-61006 80801 6/29/2009 $2,080.00 41-70-80801-61006 80806 6/29/2009 $8,840.00 41-70-80801-61006 80801 6/30/2009 $1,061.16 41-70-80801-61006 80804 7/1/2009 $4,433.90 41-70-80801-61006 80801 7/6/2009 $20,000.00 41-70-80801-61006 80801 The following works well with the 3rd variable of column (G) not being used =SUMPRODUCT(--(YEAR(Requisitions!$C$7:Requisitions!$C$1015)=2009 ),--((Requisitions!$H$7:$H$1015)=RIGHT(A3,5)),Requisit ions!$F$7:$F$1015) Your help is appreciated. |
| All times are GMT +1. The time now is 04:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com