SUMIF function referring to values on different Worksheet

I want to enter a formula on a different worksheet that contains a SUMIF
function for values on another sheet. The formula has a nested SUMIF that
works fine on the same sheet, but when I try to enter the formula on a
different sheet, the results are wrong or I get an error. Can't this be done?

SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is
causing a problem.

Show the data and the formula.

--

HTH

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


"TeeBee0831" <TeeBee0831@discussions.microsoft.com> wrote in message
news:61B045AD-066B-48A6-AA4E-29D037868C8A@microsoft.com...
> I want to enter a formula on a different worksheet that contains a SUMIF
> function for values on another sheet. The formula has a nested SUMIF that
> works fine on the same sheet, but when I try to enter the formula on a
> different sheet, the results are wrong or I get an error. Can't this be
done?
>
>

=SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost
Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,">899999",'Query-Cost
Centers'!C26:C116)

I get a result of 0 which is wrong.

"Bob Phillips" wrote:

> SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is
> causing a problem.
>
> Show the data and the formula.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "TeeBee0831" <TeeBee0831@discussions.microsoft.com> wrote in message
> news:61B045AD-066B-48A6-AA4E-29D037868C8A@microsoft.com...
> > I want to enter a formula on a different worksheet that contains a SUMIF
> > function for values on another sheet. The formula has a nested SUMIF that
> > works fine on the same sheet, but when I try to enter the formula on a
> > different sheet, the results are wrong or I get an error. Can't this be
> done?
> >
> >
>
>
>

I just created some data and tested that formula and it works for me.

It does seem odd though, as it double-counts the items between 899,999 and
100,000 because those numbers satisfy both tests Is this perhaps what you
want

=SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost
Centers'!A26:A116>899999),'Query-Cost Centers'!C26:C116)

--

HTH

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


"teebee0831" <teebee0831@discussions.microsoft.com> wrote in message
news:6231DCAA-4225-4DAF-BFC7-502F28956CB2@microsoft.com...
> =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost
> Centers'!C26:C116)+SUMIF('Query-Cost
Centers'!A26:A116,">899999",'Query-Cost
> Centers'!C26:C116)
>
> I get a result of 0 which is wrong.
>
> "Bob Phillips" wrote:
>
> > SUMIF referring to another sheet is no problem. Maybe the nested SUMIF
is
> > causing a problem.
> >
> > Show the data and the formula.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "TeeBee0831" <TeeBee0831@discussions.microsoft.com> wrote in message
> > news:61B045AD-066B-48A6-AA4E-29D037868C8A@microsoft.com...
> > > I want to enter a formula on a different worksheet that contains a
SUMIF
> > > function for values on another sheet. The formula has a nested SUMIF
that
> > > works fine on the same sheet, but when I try to enter the formula on a
> > > different sheet, the results are wrong or I get an error. Can't this
be
> > done?
> > >
> > >
> >
> >
> >

Copying this formula gave me a REF error.

"Bob Phillips" wrote:

> I just created some data and tested that formula and it works for me.
>
> It does seem odd though, as it double-counts the items between 899,999 and
> 100,000 because those numbers satisfy both tests Is this perhaps what you
> want
>
> =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost
> Centers'!A26:A116>899999),'Query-Cost Centers'!C26:C116)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "teebee0831" <teebee0831@discussions.microsoft.com> wrote in message
> news:6231DCAA-4225-4DAF-BFC7-502F28956CB2@microsoft.com...
> > =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost
> > Centers'!C26:C116)+SUMIF('Query-Cost
> Centers'!A26:A116,">899999",'Query-Cost
> > Centers'!C26:C116)
> >
> > I get a result of 0 which is wrong.
> >
> > "Bob Phillips" wrote:
> >
> > > SUMIF referring to another sheet is no problem. Maybe the nested SUMIF
> is
> > > causing a problem.
> > >
> > > Show the data and the formula.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "TeeBee0831" <TeeBee0831@discussions.microsoft.com> wrote in message
> > > news:61B045AD-066B-48A6-AA4E-29D037868C8A@microsoft.com...
> > > > I want to enter a formula on a different worksheet that contains a
> SUMIF
> > > > function for values on another sheet. The formula has a nested SUMIF
> that
> > > > works fine on the same sheet, but when I try to enter the formula on a
> > > > different sheet, the results are wrong or I get an error. Can't this
> be
> > > done?
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Did you fix the wrap-around.

--

HTH

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


"teebee0831" <teebee0831@discussions.microsoft.com> wrote in message
news:E400195A-E6F5-460F-9055-1AE1E5B8EA49@microsoft.com...
> Copying this formula gave me a REF error.
>
> "Bob Phillips" wrote:
>
> > I just created some data and tested that formula and it works for me.
> >
> > It does seem odd though, as it double-counts the items between 899,999
and
> > 100,000 because those numbers satisfy both tests Is this perhaps what
you
> > want
> >
> > =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost
> > Centers'!A26:A116>899999),'Query-Cost Centers'!C26:C116)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "teebee0831" <teebee0831@discussions.microsoft.com> wrote in message
> > news:6231DCAA-4225-4DAF-BFC7-502F28956CB2@microsoft.com...
> > > =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost
> > > Centers'!C26:C116)+SUMIF('Query-Cost
> > Centers'!A26:A116,">899999",'Query-Cost
> > > Centers'!C26:C116)
> > >
> > > I get a result of 0 which is wrong.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > SUMIF referring to another sheet is no problem. Maybe the nested
SUMIF
> > is
> > > > causing a problem.
> > > >
> > > > Show the data and the formula.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "TeeBee0831" <TeeBee0831@discussions.microsoft.com> wrote in message
> > > > news:61B045AD-066B-48A6-AA4E-29D037868C8A@microsoft.com...
> > > > > I want to enter a formula on a different worksheet that contains a
> > SUMIF
> > > > > function for values on another sheet. The formula has a nested
SUMIF
> > that
> > > > > works fine on the same sheet, but when I try to enter the formula
on a
> > > > > different sheet, the results are wrong or I get an error. Can't
this
> > be
> > > > done?
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >