=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