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