Sumif function with remote cell references

[hennis]

I have used the sumif function with remote cell references. When I ope
the worksheet and click yes to update cell refences it returns errors i
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 b
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!
==============================================