Sumproduct formula not working

[Vince]

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)=61006),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)),Requisitions!$F$7:$F$1015)

Your help is appreciated.

 

[JoeU2004]

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 -----

 

[Conan Kelly]

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)=61006)

....should be...

--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015,5)=61006)

....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)=61006)

....might have to look like this...

--(RIGHT(Requisitions!$G$7:Requisitions!$G$1015,5)="61006")

HTH,

Conan Kelly

 

[Dave Peterson]

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.

 

[Luke M]

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)="61006"),Requisitions!$F$7:$F$1015)

the other option is to place the RIGHT function within a VALUE function, if
you would rather compare numbers.

 

[Dave Peterson]

ps. Add that ,5 to the right() expression that I didn't see!

 

[Vince]

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..

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 -----

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)=61006),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)),Requisitions!$F$7:$F$1015)

Your help is appreciated.

 

[Don Guillett]

Try it like this where 61006 is in quotes and you do not need to refer
the ranges as you did.

=SUMPRODUCT(--(YEAR(Requisitions!$C$7:$C$17)=2009),--(RIGHT(Requisitions!$G$7:$G$17,5)="61006"),
--(RIGHT(Requisitions!$H$7:$H$17,5)=RIGHT(A3,5)),Requisitions!$F$7:$F$17)