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.

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

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.

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.

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

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.

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

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)),Req uisitions!$F$7:$F$17)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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