SUM(IF()) array formula in a separate workbook and multiple worksh
 

I am attempting to count rows from another workbook with 2 separate
worksheets where a specified value exists in a designated column. When the
value is found in the same cell of both worksheets, the record is being
counted only one time. My formula looks like this:

{=SUM(IF(('[Test 2008.xls]JUN-JW'!$AE$1:$AE$500="NDM")+('[Test
2008.xls]JUN-DM'!$AE$1:$AE$500="NDM"),1,0))}

Test 2008.xls=workbook
JUN-JW=worksheet 1
JUN-DM=worksheet 2
and I want to count the records where AE1 thru AE500=NDM

The problem I am experiencing is if workheet 1, cell AE58=NDM and worksheet
2, cell AE58=NDM, only one record is being counted.

Does anyone know if there is a way around this, or if this is possibly a bug
in Excel 2003 SP2?

Any thoughts would be greatly appreciated.

Thanks,
Theresa

SUM(IF()) array formula in a separate workbook and multiple worksh
 

How about just 2 COUNTIFs, non-array:
=COUNTIF('[Test2008.xls]JUN-JW'!$AE:$AE,"NDM")+COUNTIF('[Test2008.xls]JUN-DM'!$AE:$AE,"NDM")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Theresa@ArgoGroup" wrote:
I am attempting to count rows from another workbook with 2 separate
worksheets where a specified value exists in a designated column. When the
value is found in the same cell of both worksheets, the record is being
counted only one time. My formula looks like this:

{=SUM(IF(('[Test 2008.xls]JUN-JW'!$AE$1:$AE$500="NDM")+('[Test
2008.xls]JUN-DM'!$AE$1:$AE$500="NDM"),1,0))}

Test 2008.xls=workbook
JUN-JW=worksheet 1
JUN-DM=worksheet 2
and I want to count the records where AE1 thru AE500=NDM

The problem I am experiencing is if workheet 1, cell AE58=NDM and worksheet
2, cell AE58=NDM, only one record is being counted.

Does anyone know if there is a way around this, or if this is possibly a bug
in Excel 2003 SP2?

Any thoughts would be greatly appreciated.

Thanks,
Theresa

SUM(IF()) array formula in a separate workbook and multiple wo
 

Max - Thanks so much for your reply. Actually, COUNTIF was my first approach
and it does work IF both the source and destination workbooks are open.
However, that will not be the case, and that's why I needed to use an array
formula. I'm sure there are more sophisticated ways (i.e., Visual Basic,
macros, etc.) to accomplish this, but they are outside my Excel capabilities
at this time.

"Max" wrote:

How about just 2 COUNTIFs, non-array:
=COUNTIF('[Test2008.xls]JUN-JW'!$AE:$AE,"NDM")+COUNTIF('[Test2008.xls]JUN-DM'!$AE:$AE,"NDM")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Theresa@ArgoGroup" wrote:
I am attempting to count rows from another workbook with 2 separate
worksheets where a specified value exists in a designated column. When the
value is found in the same cell of both worksheets, the record is being
counted only one time. My formula looks like this:

{=SUM(IF(('[Test 2008.xls]JUN-JW'!$AE$1:$AE$500="NDM")+('[Test
2008.xls]JUN-DM'!$AE$1:$AE$500="NDM"),1,0))}

Test 2008.xls=workbook
JUN-JW=worksheet 1
JUN-DM=worksheet 2
and I want to count the records where AE1 thru AE500=NDM

The problem I am experiencing is if workheet 1, cell AE58=NDM and worksheet
2, cell AE58=NDM, only one record is being counted.

Does anyone know if there is a way around this, or if this is possibly a bug
in Excel 2003 SP2?

Any thoughts would be greatly appreciated.

Thanks,
Theresa

SUM(IF()) array formula in a separate workbook and multiple wo
 

I'm out, sorry. Perhaps you should have mentioned earlier the key point
about having it work with the source book(s) closed. Hang around for better
ideas from other responders.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Theresa@ArgoGroup" wrote in
message ...
Max - Thanks so much for your reply. Actually, COUNTIF was my first
approach
and it does work IF both the source and destination workbooks are open.
However, that will not be the case, and that's why I needed to use an
array
formula. I'm sure there are more sophisticated ways (i.e., Visual Basic,
macros, etc.) to accomplish this, but they are outside my Excel
capabilities
at this time.

SUM(IF()) array formula in a separate workbook and multiple wo
 

=sumproduct(--('[Test2008.xls]JUN-JW'!$AE1:$AE999="NDM"))
+sumproduct(--('[Test2008.xls]JUN-DM'!$AE1:$AE999="NDM"))

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



Theresa@ArgoGroup wrote:

Max - Thanks so much for your reply. Actually, COUNTIF was my first approach
and it does work IF both the source and destination workbooks are open.
However, that will not be the case, and that's why I needed to use an array
formula. I'm sure there are more sophisticated ways (i.e., Visual Basic,
macros, etc.) to accomplish this, but they are outside my Excel capabilities
at this time.

"Max" wrote:

How about just 2 COUNTIFs, non-array:
=COUNTIF('[Test2008.xls]JUN-JW'!$AE:$AE,"NDM")+COUNTIF('[Test2008.xls]JUN-DM'!$AE:$AE,"NDM")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Theresa@ArgoGroup" wrote:
I am attempting to count rows from another workbook with 2 separate
worksheets where a specified value exists in a designated column. When the
value is found in the same cell of both worksheets, the record is being
counted only one time. My formula looks like this:

{=SUM(IF(('[Test 2008.xls]JUN-JW'!$AE$1:$AE$500="NDM")+('[Test
2008.xls]JUN-DM'!$AE$1:$AE$500="NDM"),1,0))}

Test 2008.xls=workbook
JUN-JW=worksheet 1
JUN-DM=worksheet 2
and I want to count the records where AE1 thru AE500=NDM

The problem I am experiencing is if workheet 1, cell AE58=NDM and worksheet
2, cell AE58=NDM, only one record is being counted.

Does anyone know if there is a way around this, or if this is possibly a bug
in Excel 2003 SP2?

Any thoughts would be greatly appreciated.

Thanks,
Theresa

--

Dave Peterson

SUM(IF()) array formula in a separate workbook and multiple worksh
 

Use SUMPRODUCT:

=SUMPRODUCT(--('[Test
2008.xls]JUN-JW'!$AE$1:$AE$500="NDM"))+SUMPRODUCT(--('[Test
2008.xls]JUN-DM'!$AE$1:$AE$500="NDM"))

--
Biff
Microsoft Excel MVP


"Theresa@ArgoGroup" wrote in
message ...
I am attempting to count rows from another workbook with 2 separate
worksheets where a specified value exists in a designated column. When
the
value is found in the same cell of both worksheets, the record is being
counted only one time. My formula looks like this:

{=SUM(IF(('[Test 2008.xls]JUN-JW'!$AE$1:$AE$500="NDM")+('[Test
2008.xls]JUN-DM'!$AE$1:$AE$500="NDM"),1,0))}

Test 2008.xls=workbook
JUN-JW=worksheet 1
JUN-DM=worksheet 2
and I want to count the records where AE1 thru AE500=NDM

The problem I am experiencing is if workheet 1, cell AE58=NDM and
worksheet
2, cell AE58=NDM, only one record is being counted.

Does anyone know if there is a way around this, or if this is possibly a
bug
in Excel 2003 SP2?

Any thoughts would be greatly appreciated.

Thanks,
Theresa