Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
hennis
 
Posts: n/a
Default Sumif function with remote cell references


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

  #2   Report Post  
RagDyer
 
Posts: n/a
Default

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" wrote in
message ...

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax for inferred cell references donesquire Excel Worksheet Functions 4 April 4th 05 09:29 PM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 04:31 AM
Hiding a function value in a cell until function used Paul Smithson Excel Worksheet Functions 1 February 25th 05 07:19 PM
Function making cell really "empty" Arvi Laanemets Excel Worksheet Functions 2 January 31st 05 06:23 PM
copy a cell value not its function KC Mao Excel Discussion (Misc queries) 2 December 4th 04 05:30 AM


All times are GMT +1. The time now is 12:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"