sumif or sumproduct range

I've been all around and tried many suggestions but still can't make this work.

Looking at one column of numbers on a separate worksheet that should be
greater than 29 but less than 50, and if they meet this criteria I want the
sum of another column of numbers on the separate worksheet added.

I've tried
sumproduct(--active!$P:$P>29)*--(active!$P:$P<50),active!$g2:$g200)

it returns #NUM! as an answer

I have double checked and there are no errors or text entered in the G column
--
Lori

=sumproduct(--(active!$P2:$P200>29)--(active!$P2:$P200<50),active!$g2:$g200)

SP works on specific ranges, and they must be the same size

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lori Burton" <[email protected]> wrote in message
news:[email protected]...
> I've been all around and tried many suggestions but still can't make this
work.
>
> Looking at one column of numbers on a separate worksheet that should be
> greater than 29 but less than 50, and if they meet this criteria I want
the
> sum of another column of numbers on the separate worksheet added.
>
> I've tried
> sumproduct(--active!$P:$P>29)*--(active!$P:$P<50),active!$g2:$g200)
>
> it returns #NUM! as an answer
>
> I have double checked and there are no errors or text entered in the G
column
> --
> Lori

This gives a total, but not a correct one.

It's almost as if the formula is reading everything greater than 29 add
p2:p200, and then everything less than 50 add p2:p200 rather than as a
combined statement before adding G. The answer is at least 4 times as much
as it should be.

I'm needing the P: colmun filtered down to anything between the range of 30
and 50 before G is calculated.
--
Lori


"Bob Phillips" wrote:

> =sumproduct(--(active!$P2:$P200>29)--(active!$P2:$P200<50),active!$g2:$g200)
>
> SP works on specific ranges, and they must be the same size
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lori Burton" <[email protected]> wrote in message
> news:[email protected]...
> > I've been all around and tried many suggestions but still can't make this
> work.
> >
> > Looking at one column of numbers on a separate worksheet that should be
> > greater than 29 but less than 50, and if they meet this criteria I want
> the
> > sum of another column of numbers on the separate worksheet added.
> >
> > I've tried
> > sumproduct(--active!$P:$P>29)*--(active!$P:$P<50),active!$g2:$g200)
> >
> > it returns #NUM! as an answer
> >
> > I have double checked and there are no errors or text entered in the G
> column
> > --
> > Lori
>
>
>

I got it!

I changed your fomula to include an asterisk and it calculated perfectly.

Thank you!
--
Lori


"Bob Phillips" wrote:

> =sumproduct(--(active!$P2:$P200>29)--(active!$P2:$P200<50),active!$g2:$g200)
>
> SP works on specific ranges, and they must be the same size
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lori Burton" <[email protected]> wrote in message
> news:[email protected]...
> > I've been all around and tried many suggestions but still can't make this
> work.
> >
> > Looking at one column of numbers on a separate worksheet that should be
> > greater than 29 but less than 50, and if they meet this criteria I want
> the
> > sum of another column of numbers on the separate worksheet added.
> >
> > I've tried
> > sumproduct(--active!$P:$P>29)*--(active!$P:$P<50),active!$g2:$g200)
> >
> > it returns #NUM! as an answer
> >
> > I have double checked and there are no errors or text entered in the G
> column
> > --
> > Lori
>
>
>

typo, it should be

=sumproduct(--(active!$P2:$P200>29),--(active!$P2:$P200<50),active!$g2:$g200
)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lori Burton" <[email protected]> wrote in message
news:[email protected]...
> This gives a total, but not a correct one.
>
> It's almost as if the formula is reading everything greater than 29 add
> p2:p200, and then everything less than 50 add p2:p200 rather than as a
> combined statement before adding G. The answer is at least 4 times as
much
> as it should be.
>
> I'm needing the P: colmun filtered down to anything between the range of
30
> and 50 before G is calculated.
> --
> Lori
>
>
> "Bob Phillips" wrote:
>
> >
=sumproduct(--(active!$P2:$P200>29)--(active!$P2:$P200<50),active!$g2:$g200)
> >
> > SP works on specific ranges, and they must be the same size
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Lori Burton" <[email protected]> wrote in message
> > news:[email protected]...
> > > I've been all around and tried many suggestions but still can't make
this
> > work.
> > >
> > > Looking at one column of numbers on a separate worksheet that should
be
> > > greater than 29 but less than 50, and if they meet this criteria I
want
> > the
> > > sum of another column of numbers on the separate worksheet added.
> > >
> > > I've tried
> > > sumproduct(--active!$P:$P>29)*--(active!$P:$P<50),active!$g2:$g200)
> > >
> > > it returns #NUM! as an answer
> > >
> > > I have double checked and there are no errors or text entered in the G
> > column
> > > --
> > > Lori
> >
> >
> >