SUMIF function referring to values on different Worksheet

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?
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Wrap around?

"Bob Phillips" wrote:

> 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?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Sorry, I did fix this but the formula still doesn't work. I get an REF# error.

"teebee0831" wrote:

> Wrap around?
>
> "Bob Phillips" wrote:
>
> > 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?
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

I just put 999999 in A26, and 3 in C26 and got a value of 3 returned.

I could force a #REF if I took one of the spaces out of the sheet name,
which might be what happened to you in correcting the NG wrap-around.

--

HTH

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


"teebee0831" <teebee0831@discussions.microsoft.com> wrote in message
news:5A330090-4C7D-4C7B-8C8E-767DF4617E23@microsoft.com...
> Sorry, I did fix this but the formula still doesn't work. I get an REF#
error.
>
> "teebee0831" wrote:
>
> > Wrap around?
> >
> > "Bob Phillips" wrote:
> >
> > > 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?
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

Thanks for the help, but nothing seems to work.

"Bob Phillips" wrote:

> I just put 999999 in A26, and 3 in C26 and got a value of 3 returned.
>
> I could force a #REF if I took one of the spaces out of the sheet name,
> which might be what happened to you in correcting the NG wrap-around.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "teebee0831" <teebee0831@discussions.microsoft.com> wrote in message
> news:5A330090-4C7D-4C7B-8C8E-767DF4617E23@microsoft.com...
> > Sorry, I did fix this but the formula still doesn't work. I get an REF#
> error.
> >
> > "teebee0831" wrote:
> >
> > > Wrap around?
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > 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?
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

Final try. Send me the workbook?

bob dot phillips at tiscali dot co dot uk

do the obvious.

--

HTH

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


"teebee0831" <teebee0831@discussions.microsoft.com> wrote in message
news:9F911900-3818-412B-92C4-E14A49ADAD86@microsoft.com...
> Thanks for the help, but nothing seems to work.
>
> "Bob Phillips" wrote:
>
> > I just put 999999 in A26, and 3 in C26 and got a value of 3 returned.
> >
> > I could force a #REF if I took one of the spaces out of the sheet name,
> > which might be what happened to you in correcting the NG wrap-around.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "teebee0831" <teebee0831@discussions.microsoft.com> wrote in message
> > news:5A330090-4C7D-4C7B-8C8E-767DF4617E23@microsoft.com...
> > > Sorry, I did fix this but the formula still doesn't work. I get an
REF#
> > error.
> > >
> > > "teebee0831" wrote:
> > >
> > > > Wrap around?
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > 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?
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >