Sumif function with remote cell references

[hennis]

I have used the sumif function with remote cell references. When I open the worksheet and click yes to update cell refences it returns errors in the cells but if I open the other worksheet the references update.

Does anyone know how to use sumif and allow the remote references to be updated without opening the worksheet that contains the references.

Thanks.....

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>

[RagDyer]

You *can't* use Sumif(), but you *can* use a combination of Sum() and If()
in an *array* formula.

Revise this formula:

=SUMIF(A1:A20,C1,B1:B20)

To this *array* formula:

=SUM(IF(A1:A20=C1,B1:B20)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Of course, you'll need to enter the path to the other WB.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"hennis" <[email protected]> wrote in
message news:[email protected]...
>
> I have used the sumif function with remote cell references. When I open
> the worksheet and click yes to update cell refences it returns errors in
> the cells but if I open the other worksheet the references update.
>
> Does anyone know how to use sumif and allow the remote references to be
> updated without opening the worksheet that contains the references.
>
> Thanks.....
>
>
> --
> hennis
> ------------------------------------------------------------------------
> hennis's Profile:
http://www.excelforum.com/member.php...o&userid=26207
> View this thread: http://www.excelforum.com/showthread...hreadid=395133
>