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