Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula - sum with multiple criteria | Excel Discussion (Misc queries) | |||
Array Formula : Problems with sharing workbook | Excel Discussion (Misc queries) | |||
shared workbook appending/merging rows of data from several worksh | Excel Discussion (Misc queries) | |||
Index,Match table array in separate workbook | Excel Worksheet Functions | |||
Why do multiple circular references affect each other in a worksh. | Excel Discussion (Misc queries) |