| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif error using multiple workbooks
I have a countif formula that I am using to keep track of how many times
someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
COUNTIF doesn't work when it references an external workbook that is closed.
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Melody4572" wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
Try something like this with the workbook open (easier to create the formula),
then close that workbook. =sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5)) +more of the same.... ps. Applicant has two p's. Melody4572 wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
Yes, I know, but when I try the countproduct it gives me the same error also.
I was hoping someone else ran into this issue and have a work around or even another formula that will work. :) "Dave F" wrote: COUNTIF doesn't work when it references an external workbook that is closed. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Melody4572" wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
OK, my first question: If you click the = next to the formula bar, is the
error in only one, some, or all of the range references when you use SUMPRODUCT? Second, just to be sure, are both files in the same folder? If not, when the 2006 workbook is closed, does the SUMPRODUCT update to show the full path to the 2006 file? "Melody4572" wrote: Yes, I know, but when I try the countproduct it gives me the same error also. I was hoping someone else ran into this issue and have a work around or even another formula that will work. :) "Dave F" wrote: COUNTIF doesn't work when it references an external workbook that is closed. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Melody4572" wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
Yes both files are in the same folder, there is no error messages in the
formula bar. It shows the formula just find, the end resualts are Value#. When I tried Sumproduct for the smae worksheet I get the smae error that I do for the linked one. I wonder if Sumproduct doesn't like names? "Sean Timmons" wrote: OK, my first question: If you click the = next to the formula bar, is the error in only one, some, or all of the range references when you use SUMPRODUCT? Second, just to be sure, are both files in the same folder? If not, when the 2006 workbook is closed, does the SUMPRODUCT update to show the full path to the 2006 file? "Melody4572" wrote: Yes, I know, but when I try the countproduct it gives me the same error also. I was hoping someone else ran into this issue and have a work around or even another formula that will work. :) "Dave F" wrote: COUNTIF doesn't work when it references an external workbook that is closed. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Melody4572" wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
LOL on the applicant spelling. Its been over a year and no one noticed it
until you pointed it out :) As for the formula I did try that. I changed the formulas, rewrote the formulas deleted the column readded a columns and started from scratch with the product sum formula. Still not working correctly. I wonder if I turned it into an array formula if it would work then? "Dave Peterson" wrote: Try something like this with the workbook open (easier to create the formula), then close that workbook. =sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5)) +more of the same.... ps. Applicant has two p's. Melody4572 wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
You could use
=sum(if(...)) as an array formula. But it should work the same way as =sumproduct() If I were you, I'd get one portion of that long formula working. Then build on that. If you try just a single addend and still have trouble, post back the (smaller) formula that you tried. Melody4572 wrote: LOL on the applicant spelling. Its been over a year and no one noticed it until you pointed it out :) As for the formula I did try that. I changed the formulas, rewrote the formulas deleted the column readded a columns and started from scratch with the product sum formula. Still not working correctly. I wonder if I turned it into an array formula if it would work then? "Dave Peterson" wrote: Try something like this with the workbook open (easier to create the formula), then close that workbook. =sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5)) +more of the same.... ps. Applicant has two p's. Melody4572 wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
I tried the sumproduct for one month. it still isnt working.
=SUMPRODUCT(COUNTIF('C:\Documents and Settings\Owner\Desktop\Applicant\[Applicant Info 2006.xls]January aps'!$B$4:$B$400,$B22)) With both spreadsheets open the path is not in the formula, but with 2006 closed it will show the correct path (yes I am working on it from home and not at work thus the reason for the C: blah blah instead S:blah blah.) "Dave Peterson" wrote: You could use =sum(if(...)) as an array formula. But it should work the same way as =sumproduct() If I were you, I'd get one portion of that long formula working. Then build on that. If you try just a single addend and still have trouble, post back the (smaller) formula that you tried. Melody4572 wrote: LOL on the applicant spelling. Its been over a year and no one noticed it until you pointed it out :) As for the formula I did try that. I changed the formulas, rewrote the formulas deleted the column readded a columns and started from scratch with the product sum formula. Still not working correctly. I wonder if I turned it into an array formula if it would work then? "Dave Peterson" wrote: Try something like this with the workbook open (easier to create the formula), then close that workbook. =sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5)) +more of the same.... ps. Applicant has two p's. Melody4572 wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
=sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5))
Was my suggestion. I didn't include =countif() and I used =$b5 near the end. I'd build the formula with the workbook open and let excel worry about the syntax when you close that "sending" workbook. Melody4572 wrote: I tried the sumproduct for one month. it still isnt working. =SUMPRODUCT(COUNTIF('C:\Documents and Settings\Owner\Desktop\Applicant\[Applicant Info 2006.xls]January aps'!$B$4:$B$400,$B22)) With both spreadsheets open the path is not in the formula, but with 2006 closed it will show the correct path (yes I am working on it from home and not at work thus the reason for the C: blah blah instead S:blah blah.) "Dave Peterson" wrote: You could use =sum(if(...)) as an array formula. But it should work the same way as =sumproduct() If I were you, I'd get one portion of that long formula working. Then build on that. If you try just a single addend and still have trouble, post back the (smaller) formula that you tried. Melody4572 wrote: LOL on the applicant spelling. Its been over a year and no one noticed it until you pointed it out :) As for the formula I did try that. I changed the formulas, rewrote the formulas deleted the column readded a columns and started from scratch with the product sum formula. Still not working correctly. I wonder if I turned it into an array formula if it would work then? "Dave Peterson" wrote: Try something like this with the workbook open (easier to create the formula), then close that workbook. =sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5)) +more of the same.... ps. Applicant has two p's. Melody4572 wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
Life becomes bright and sunny once you slow down and read the fine print.
Sorry about that. I had misunderstood your original post. It works perfect now. Thanks Dave! "Dave Peterson" wrote: =sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5)) Was my suggestion. I didn't include =countif() and I used =$b5 near the end. I'd build the formula with the workbook open and let excel worry about the syntax when you close that "sending" workbook. Melody4572 wrote: I tried the sumproduct for one month. it still isnt working. =SUMPRODUCT(COUNTIF('C:\Documents and Settings\Owner\Desktop\Applicant\[Applicant Info 2006.xls]January aps'!$B$4:$B$400,$B22)) With both spreadsheets open the path is not in the formula, but with 2006 closed it will show the correct path (yes I am working on it from home and not at work thus the reason for the C: blah blah instead S:blah blah.) "Dave Peterson" wrote: You could use =sum(if(...)) as an array formula. But it should work the same way as =sumproduct() If I were you, I'd get one portion of that long formula working. Then build on that. If you try just a single addend and still have trouble, post back the (smaller) formula that you tried. Melody4572 wrote: LOL on the applicant spelling. Its been over a year and no one noticed it until you pointed it out :) As for the formula I did try that. I changed the formulas, rewrote the formulas deleted the column readded a columns and started from scratch with the product sum formula. Still not working correctly. I wonder if I turned it into an array formula if it would work then? "Dave Peterson" wrote: Try something like this with the workbook open (easier to create the formula), then close that workbook. =sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5)) +more of the same.... ps. Applicant has two p's. Melody4572 wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif error using multiple workbooks
Glad you got it working!
Melody4572 wrote: Life becomes bright and sunny once you slow down and read the fine print. Sorry about that. I had misunderstood your original post. It works perfect now. Thanks Dave! "Dave Peterson" wrote: =sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5)) Was my suggestion. I didn't include =countif() and I used =$b5 near the end. I'd build the formula with the workbook open and let excel worry about the syntax when you close that "sending" workbook. Melody4572 wrote: I tried the sumproduct for one month. it still isnt working. =SUMPRODUCT(COUNTIF('C:\Documents and Settings\Owner\Desktop\Applicant\[Applicant Info 2006.xls]January aps'!$B$4:$B$400,$B22)) With both spreadsheets open the path is not in the formula, but with 2006 closed it will show the correct path (yes I am working on it from home and not at work thus the reason for the C: blah blah instead S:blah blah.) "Dave Peterson" wrote: You could use =sum(if(...)) as an array formula. But it should work the same way as =sumproduct() If I were you, I'd get one portion of that long formula working. Then build on that. If you try just a single addend and still have trouble, post back the (smaller) formula that you tried. Melody4572 wrote: LOL on the applicant spelling. Its been over a year and no one noticed it until you pointed it out :) As for the formula I did try that. I changed the formulas, rewrote the formulas deleted the column readded a columns and started from scratch with the product sum formula. Still not working correctly. I wonder if I turned it into an array formula if it would work then? "Dave Peterson" wrote: Try something like this with the workbook open (easier to create the formula), then close that workbook. =sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5)) +more of the same.... ps. Applicant has two p's. Melody4572 wrote: I have a countif formula that I am using to keep track of how many times someone comes in to apply. It is set up per month per sheet. To keep track of multiple applicants I have a column that shows if they have been here before. We have had several people from last year come back so now I need to link it to last years information. As long as the workbooks are both open, no problem. Once 2006 is closed the column that holds this information all turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately errs out to again the VALUE#. The formula that works is: =COUNTIF('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December aps'!$B$4:$B$400,$B5))) But I truly would like it to work without opening the other workbook. I know what a baby :) -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple workbooks on Taskbar in Excel 97 | Excel Discussion (Misc queries) | |||
| How do I use VLOOKUP to ref multiple workbooks with multiple tabs? | Excel Discussion (Misc queries) | |||
| COUNTIF across multiple sheets | Excel Worksheet Functions | |||
| Creating multiple workbooks from summary workbook | Excel Discussion (Misc queries) | |||
| AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
Linear Mode
