Array Formula : Problems with sharing workbook
 

I currently use an array formula to capture data from a specific
location. The problem is that the array formula prevents the workbook
being shared by more than one person. Please can you advice on what to
do to the formula below i.e. to use non array instead tol capture the
same data. Below is an illustration of the type of task the formula id
currently used for;

SOURCE DATA
Column A B C
1
2 KMAA 5101 450
3 KMAA 5101 320
4 KMSA 4102 987
5 KMSD 5454 189


RESULT AREA (this is where the array formula is)
Column A B C
1 KMAA
2
3 5101 {=SUM('[IF(Book1.xls]SOURCE DATA'!A2:A5=$B
$1,IF(Book1.xls]SOURCE DATA'!B2:B5=$A3,Book1.xls]SOURCE DATA'!
C2:C5)))}

4
5

I have tried everything to change the above to non array so that that
the workbook could be shared with no success. Any advice would be much
appreciated.

Many thanks.

Array Formula : Problems with sharing workbook
 

First, it's better to post the working formula--just copy from the formula bar
and paste into the message. It makes it easier for people to help.

Second, sometimes you can use =sumproduct() for =sum(if())'s:

=SUMPRODUCT(--('[book1.xls]Source Data'!A2:A5=$B$1),
--('[book1.xls]Source Data'!B2:B5=$A3),
('[book1.xls]Source Data'!C2:C5))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Ola2B wrote:

I currently use an array formula to capture data from a specific
location. The problem is that the array formula prevents the workbook
being shared by more than one person. Please can you advice on what to
do to the formula below i.e. to use non array instead tol capture the
same data. Below is an illustration of the type of task the formula id
currently used for;

SOURCE DATA
Column A B C
1
2 KMAA 5101 450
3 KMAA 5101 320
4 KMSA 4102 987
5 KMSD 5454 189

RESULT AREA (this is where the array formula is)
Column A B C
1 KMAA
2
3 5101 {=SUM('[IF(Book1.xls]SOURCE DATA'!A2:A5=$B
$1,IF(Book1.xls]SOURCE DATA'!B2:B5=$A3,Book1.xls]SOURCE DATA'!
C2:C5)))}

4
5

I have tried everything to change the above to non array so that that
the workbook could be shared with no success. Any advice would be much
appreciated.

Many thanks.

--

Dave Peterson