Sumproduct error caused by too many curly brackets?
 

Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA.
iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT
CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE
RANGE OF VALUES IN COLLUMN ge.
aNY IDEAS ????
tHANKS
rr1

=SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)))

Sumproduct error caused by too many curly brackets?
 

Try the below

=SUMPRODUCT(
($FP$10:$FP$89={"W","SA","SA+","ST"})*
($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))
/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))

If this post helps click Yes
---------------
Jacob Skaria


"Romileyrunner1" wrote:

Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA.
iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT
CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE
RANGE OF VALUES IN COLLUMN ge.
aNY IDEAS ????
tHANKS
rr1

=SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)))

Sumproduct error caused by too many curly brackets?
 

tried this thanks, but still have the same errors.
any further suggestions?
rr1
thanks.

"Jacob Skaria" wrote:

Try the below

=SUMPRODUCT(
($FP$10:$FP$89={"W","SA","SA+","ST"})*
($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))
/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))

If this post helps click Yes
---------------
Jacob Skaria


"Romileyrunner1" wrote:

Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA.
iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT
CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE
RANGE OF VALUES IN COLLUMN ge.
aNY IDEAS ????
tHANKS
rr1

=SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)))

Sumproduct error caused by too many curly brackets?
 

=SUMPRODUCT(
($FP$10:$FP$89={"W","SA","SA+","ST"})*
(isnumber(match($GE$10:$GE$89,{"4c","4c+","4b","4b +","4a","4a+","5c","5c+","5b","5b+","5a","5a+","6c "},0))))
/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))


"Romileyrunner1" wrote:

Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA.
iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT
CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE
RANGE OF VALUES IN COLLUMN ge.
aNY IDEAS ????
tHANKS
rr1

=SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)))

Sumproduct error caused by too many curly brackets?
 

Hi,

Try this

=sumproduct((isnumber(match($FP$10:$FP$89,A2:A5,0) )*(isnumber(match($GE$10:$GE$89,B2:B14,0))))/sumproduct(isnumber(match($FP$10:$FP$89,A2:A5,0))* ($GE$10:$GE$890))

A2:A5 holds W, S, SA+ and ST. B2:B14 holds 4c, 4c+ etc.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Romileyrunner1" wrote in message
...
Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA.
iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT
CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR
THE
RANGE OF VALUES IN COLLUMN ge.
aNY IDEAS ????
tHANKS
rr1

=SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)))

Sumproduct error caused by too many curly brackets?
 

Great Work Ashish: works fine now: never used "(isnumber(match ..." before
Many Thanks
RR1

"Ashish Mathur" wrote:

Hi,

Try this

=sumproduct((isnumber(match($FP$10:$FP$89,A2:A5,0) )*(isnumber(match($GE$10:$GE$89,B2:B14,0))))/sumproduct(isnumber(match($FP$10:$FP$89,A2:A5,0))* ($GE$10:$GE$890))

A2:A5 holds W, S, SA+ and ST. B2:B14 holds 4c, 4c+ etc.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Romileyrunner1" wrote in message
...
Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA.
iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT
CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR
THE
RANGE OF VALUES IN COLLUMN ge.
aNY IDEAS ????
tHANKS
rr1

=SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)))

Sumproduct error caused by too many curly brackets?
 

Many Thanks Teethless Mama:
works just fine now.
Good on ya.
RR1

"Teethless mama" wrote:

=SUMPRODUCT(
($FP$10:$FP$89={"W","SA","SA+","ST"})*
(isnumber(match($GE$10:$GE$89,{"4c","4c+","4b","4b +","4a","4a+","5c","5c+","5b","5b+","5a","5a+","6c "},0))))
/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))


"Romileyrunner1" wrote:

Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA.
iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT
CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE
RANGE OF VALUES IN COLLUMN ge.
aNY IDEAS ????
tHANKS
rr1

=SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)))

Sumproduct error caused by too many curly brackets?
 

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Romileyrunner1" wrote in message
...
Great Work Ashish: works fine now: never used "(isnumber(match ..." before
Many Thanks
RR1

"Ashish Mathur" wrote:

Hi,

Try this

=sumproduct((isnumber(match($FP$10:$FP$89,A2:A5,0) )*(isnumber(match($GE$10:$GE$89,B2:B14,0))))/sumproduct(isnumber(match($FP$10:$FP$89,A2:A5,0))* ($GE$10:$GE$890))

A2:A5 holds W, S, SA+ and ST. B2:B14 holds 4c, 4c+ etc.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Romileyrunner1" wrote in
message
...
Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA.
iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE.
iT
CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS
FOR
THE
RANGE OF VALUES IN COLLUMN ge.
aNY IDEAS ????
tHANKS
rr1

=SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)))