sumproduct producing #VALUE!

Perhaps try also:

=SUMPRODUCT((Table1!J2:J5='Summary Results
PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"philcud" <philcuddy@gmail.com> wrote in message
news:1122299409.694686.92540@f14g2000cwb.googlegroups.com...
> hi all,
> i have the following formula
>
> =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5))
>
> in effect this is a simple sumif, the reason i'm using sumproduct is
> that i am going to expand it to more than one criteria.
>
> my problem lies in the range i am summing (TABLE1!N2:N5), contains text
> and error values (first example in cell j5, if i shrink the range to
> only look down to cell j4, it works)
>
> i have tried using
>
> '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results
> PC'!B$13)*ISNUMBER((TABLE1!N2:N5)))
>
> but this gives me the count of number cells in the range, not the
> summed range needed.
>
> have googled this for wuite some time and have come up with no solution
> - over to the group.
>
> Thanks in advance.
>

That's okay for text Max, but won't work with an error result.

Bob

"Max" <demechanik@yahoo.com> wrote in message
news:%236GJZRSkFHA.2156@TK2MSFTNGP14.phx.gbl...
> Perhaps try also:
>
> =SUMPRODUCT((Table1!J2:J5='Summary Results
> PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "philcud" <philcuddy@gmail.com> wrote in message
> news:1122299409.694686.92540@f14g2000cwb.googlegroups.com...
> > hi all,
> > i have the following formula
> >
> > =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5))
> >
> > in effect this is a simple sumif, the reason i'm using sumproduct is
> > that i am going to expand it to more than one criteria.
> >
> > my problem lies in the range i am summing (TABLE1!N2:N5), contains text
> > and error values (first example in cell j5, if i shrink the range to
> > only look down to cell j4, it works)
> >
> > i have tried using
> >
> > '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results
> > PC'!B$13)*ISNUMBER((TABLE1!N2:N5)))
> >
> > but this gives me the count of number cells in the range, not the
> > summed range needed.
> >
> > have googled this for wuite some time and have come up with no solution
> > - over to the group.
> >
> > Thanks in advance.
> >
>
>

That's okay for text Max, but won't work with an error result.

Bob

"Max" <demechanik@yahoo.com> wrote in message
news:%236GJZRSkFHA.2156@TK2MSFTNGP14.phx.gbl...
> Perhaps try also:
>
> =SUMPRODUCT((Table1!J2:J5='Summary Results
> PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "philcud" <philcuddy@gmail.com> wrote in message
> news:1122299409.694686.92540@f14g2000cwb.googlegroups.com...
> > hi all,
> > i have the following formula
> >
> > =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5))
> >
> > in effect this is a simple sumif, the reason i'm using sumproduct is
> > that i am going to expand it to more than one criteria.
> >
> > my problem lies in the range i am summing (TABLE1!N2:N5), contains text
> > and error values (first example in cell j5, if i shrink the range to
> > only look down to cell j4, it works)
> >
> > i have tried using
> >
> > '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results
> > PC'!B$13)*ISNUMBER((TABLE1!N2:N5)))
> >
> > but this gives me the count of number cells in the range, not the
> > summed range needed.
> >
> > have googled this for wuite some time and have come up with no solution
> > - over to the group.
> >
> > Thanks in advance.
> >
>
>

"Bob Phillips" wrote
> That's okay for text Max, but won't work with an error result.

Yes, you're right. Thanks. Should have tested more thoroughly
Looks like we've to go the array route (similar to Aladin's 2nd formula):
=SUM(IF((Table1!J2:J5='Summary Results
PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

many thanks for all your help, in the end i used:
=SUMPRODUCT((TABLE1!$J$2:$J$2000=$B$45)*(TABLE1!$V$2:$V$2000="n")*(ISNUMBER((TABLE1!$N$2:$N$2000))),TABLE1!$N$2:$N$2000)

(note i have increased the range sizes and criteria)

as it turned out, what i thought were na errors, was actually text
inputted by the user.

Thanks again

Glad to hear that !
Thanks for feeding back to us ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Glad to hear that !
Thanks for feeding back to us ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Glad to hear that !
Thanks for feeding back to us ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Glad to hear that !
Thanks for feeding back to us ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Glad to hear that !
Thanks for feeding back to us ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

many thanks for all your help, in the end i used:
=SUMPRODUCT((TABLE1!$J$2:$J$2000=$B$45)*(TABLE1!$V$2:$V$2000="n")*(ISNUMBER((TABLE1!$N$2:$N$2000))),TABLE1!$N$2:$N$2000)

(note i have increased the range sizes and criteria)

as it turned out, what i thought were na errors, was actually text
inputted by the user.

Thanks again

many thanks for all your help, in the end i used:
=SUMPRODUCT((TABLE1!$J$2:$J$2000=$B$45)*(TABLE1!$V$2:$V$2000="n")*(ISNUMBER((TABLE1!$N$2:$N$2000))),TABLE1!$N$2:$N$2000)

(note i have increased the range sizes and criteria)

as it turned out, what i thought were na errors, was actually text
inputted by the user.

Thanks again

many thanks for all your help, in the end i used:
=SUMPRODUCT((TABLE1!$J$2:$J$2000=$B$45)*(TABLE1!$V$2:$V$2000="n")*(ISNUMBER((TABLE1!$N$2:$N$2000))),TABLE1!$N$2:$N$2000)

(note i have increased the range sizes and criteria)

as it turned out, what i thought were na errors, was actually text
inputted by the user.

Thanks again

many thanks for all your help, in the end i used:
=SUMPRODUCT((TABLE1!$J$2:$J$2000=$B$45)*(TABLE1!$V$2:$V$2000="n")*(ISNUMBER((TABLE1!$N$2:$N$2000))),TABLE1!$N$2:$N$2000)

(note i have increased the range sizes and criteria)

as it turned out, what i thought were na errors, was actually text
inputted by the user.

Thanks again

"Bob Phillips" wrote
> That's okay for text Max, but won't work with an error result.

Yes, you're right. Thanks. Should have tested more thoroughly
Looks like we've to go the array route (similar to Aladin's 2nd formula):
=SUM(IF((Table1!J2:J5='Summary Results
PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

"Bob Phillips" wrote
> That's okay for text Max, but won't work with an error result.

Yes, you're right. Thanks. Should have tested more thoroughly
Looks like we've to go the array route (similar to Aladin's 2nd formula):
=SUM(IF((Table1!J2:J5='Summary Results
PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

"Bob Phillips" wrote
> That's okay for text Max, but won't work with an error result.

Yes, you're right. Thanks. Should have tested more thoroughly
Looks like we've to go the array route (similar to Aladin's 2nd formula):
=SUM(IF((Table1!J2:J5='Summary Results
PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

"Bob Phillips" wrote
> That's okay for text Max, but won't work with an error result.

Yes, you're right. Thanks. Should have tested more thoroughly
Looks like we've to go the array route (similar to Aladin's 2nd formula):
=SUM(IF((Table1!J2:J5='Summary Results
PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

That's okay for text Max, but won't work with an error result.

Bob

"Max" <demechanik@yahoo.com> wrote in message
news:%236GJZRSkFHA.2156@TK2MSFTNGP14.phx.gbl...
> Perhaps try also:
>
> =SUMPRODUCT((Table1!J2:J5='Summary Results
> PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "philcud" <philcuddy@gmail.com> wrote in message
> news:1122299409.694686.92540@f14g2000cwb.googlegroups.com...
> > hi all,
> > i have the following formula
> >
> > =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5))
> >
> > in effect this is a simple sumif, the reason i'm using sumproduct is
> > that i am going to expand it to more than one criteria.
> >
> > my problem lies in the range i am summing (TABLE1!N2:N5), contains text
> > and error values (first example in cell j5, if i shrink the range to
> > only look down to cell j4, it works)
> >
> > i have tried using
> >
> > '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results
> > PC'!B$13)*ISNUMBER((TABLE1!N2:N5)))
> >
> > but this gives me the count of number cells in the range, not the
> > summed range needed.
> >
> > have googled this for wuite some time and have come up with no solution
> > - over to the group.
> >
> > Thanks in advance.
> >
>
>

That's okay for text Max, but won't work with an error result.

Bob

"Max" <demechanik@yahoo.com> wrote in message
news:%236GJZRSkFHA.2156@TK2MSFTNGP14.phx.gbl...
> Perhaps try also:
>
> =SUMPRODUCT((Table1!J2:J5='Summary Results
> PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "philcud" <philcuddy@gmail.com> wrote in message
> news:1122299409.694686.92540@f14g2000cwb.googlegroups.com...
> > hi all,
> > i have the following formula
> >
> > =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5))
> >
> > in effect this is a simple sumif, the reason i'm using sumproduct is
> > that i am going to expand it to more than one criteria.
> >
> > my problem lies in the range i am summing (TABLE1!N2:N5), contains text
> > and error values (first example in cell j5, if i shrink the range to
> > only look down to cell j4, it works)
> >
> > i have tried using
> >
> > '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results
> > PC'!B$13)*ISNUMBER((TABLE1!N2:N5)))
> >
> > but this gives me the count of number cells in the range, not the
> > summed range needed.
> >
> > have googled this for wuite some time and have come up with no solution
> > - over to the group.
> >
> > Thanks in advance.
> >
>
>

That's okay for text Max, but won't work with an error result.

Bob

"Max" <demechanik@yahoo.com> wrote in message
news:%236GJZRSkFHA.2156@TK2MSFTNGP14.phx.gbl...
> Perhaps try also:
>
> =SUMPRODUCT((Table1!J2:J5='Summary Results
> PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "philcud" <philcuddy@gmail.com> wrote in message
> news:1122299409.694686.92540@f14g2000cwb.googlegroups.com...
> > hi all,
> > i have the following formula
> >
> > =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5))
> >
> > in effect this is a simple sumif, the reason i'm using sumproduct is
> > that i am going to expand it to more than one criteria.
> >
> > my problem lies in the range i am summing (TABLE1!N2:N5), contains text
> > and error values (first example in cell j5, if i shrink the range to
> > only look down to cell j4, it works)
> >
> > i have tried using
> >
> > '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results
> > PC'!B$13)*ISNUMBER((TABLE1!N2:N5)))
> >
> > but this gives me the count of number cells in the range, not the
> > summed range needed.
> >
> > have googled this for wuite some time and have come up with no solution
> > - over to the group.
> >
> > Thanks in advance.
> >
>
>