Sumproduct formula not working

  • Thread starter Thread starter Vince
  • Start date Start date
V

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.
 
Vince said:
The following formula returns 0.
[....]
The following works well with the 3rd variable of column (G) not being
used
Click to expand...

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,

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
 
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.
 
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.
 
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 said:
Vince said:
The following formula returns 0.
[....]
The following works well with the 3rd variable of column (G) not being
used
Click to expand...

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 said:
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.
Click to expand...
Click to expand...
 
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)
 
Back
Top