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

[Theresa@ArgoGroup]

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

 

[Max]

How about just 2 COUNTIFs, non-array:
=COUNTIF('[Test2008.xls]JUN-JW'!$AE:$AE,"NDM")+COUNTIF('[Test2008.xls]JUN-DM'!$AE:$AE,"NDM")

 

[Theresa@ArgoGroup]

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.

How about just 2 COUNTIFs, non-array:
=COUNTIF('[Test2008.xls]JUN-JW'!$AE:$AE,"NDM")+COUNTIF('[Test2008.xls]JUN-DM'!$AE:$AE,"NDM")

---

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

 

[Max]

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.

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

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.

How about just 2 COUNTIFs, non-array:
=COUNTIF('[Test2008.xls]JUN-JW'!$AE:$AE,"NDM")+COUNTIF('[Test2008.xls]JUN-DM'!$AE:$AE,"NDM")

---

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

 

[T. Valko]

Use SUMPRODUCT:

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