If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting cells between 2 values
Basically, i'm trying to count the number of cells that have dates
between 1/1/04 and 1/8/04, but all the cells that are being counted are in one colunm in another workbook. So i'm at a loss of how I can do this...Any help? --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Counting cells between 2 values
=SUMPRODUCT(([Book2]Sheet1!$A$1:$A$10=DATE(2004,1,1))*([Book2]Sheet1!$A$1:$
A$10=DATE(2004,8,1))) By the way, I am assuming UK style dates, so 1/8/04 is 1st August? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "abxy " wrote in message ... Basically, i'm trying to count the number of cells that have dates between 1/1/04 and 1/8/04, but all the cells that are being counted are in one colunm in another workbook. So i'm at a loss of how I can do this...Any help? --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Counting cells between 2 values
Try:
=COUNTIF([Book5]Sheet1!$A:$A,"=1/1/04")-COUNTIF([Book5] Sheet1!$A:$A,"1/8/04") Change the wb name, sheet name, and col. reference if needed. Format the formula sign as number. HTH Jason Atlanta, GA -----Original Message----- Basically, i'm trying to count the number of cells that have dates between 1/1/04 and 1/8/04, but all the cells that are being counted are in one colunm in another workbook. So i'm at a loss of how I can do this...Any help? --- Message posted from http://www.ExcelForum.com/ . |
#4
|
|||
|
|||
Counting cells between 2 values
Basically, i'm trying to count the number of cells that have dates
between 1/1/04 and 1/8/04, but all the cells that are being counted are in one colunm in another workbook. So i'm at a loss of how I can do this...Any help? --- Message posted from http://www.ExcelForum.com/ Hi try =SUMPRODUCT(('[Book1.xls]Sheet1'!$A$1:$A$999=DATE(2004,1,1))*('[Book1. xls]Sheet1'!$A$1:$A$999=DATE(2004,1,8))) counts your entries between January 1st and January 8th Frank |
#5
|
|||
|
|||
Counting cells between 2 values
"abxy " wrote...
it's not working What's your *EXACT* formula? -- To top-post is human, to bottom-post and snip is sublime. |
#6
|
|||
|
|||
Counting cells between 2 values
it's not working
--- Message posted from http://www.ExcelForum.com/ Hi a little bit more information would be helpful :-) - did the formula return an error? - how did you adapt the formual (change the workbook name, etc.) - is the other workbook closeD (if yes, you have to include the pathname for the reference / try opening the other workbook and see if you get the results you want) - have you changed our assumed range Frank |
#7
|
|||
|
|||
Counting cells between 2 values
i'm sorry, i wrote "it's not working" after the first 2 replies ...I
swore I saw that appear as the 3rd reply in this thread, I'll try out you all's suggestions right now...hopefully they'll work. Frank Kabel is always right on the money. --- Message posted from http://www.ExcelForum.com/ |
#8
|
|||
|
|||
Counting cells between 2 values
Hi
AFAIK SUMPRODUCT is not able to process 3D arrays. One way could be the use of the Add-in MOREFUNC.XLL (http://longre.free.fr/english). The implemented function THREED converts a 3D array to a 2D array: Your formula would look like: =SUMPRODUCT((THREED('[Book1.xls]Sheet1:sheet30'!$A$1:$A$999)=DATE(2004 ,1,1))*(THREED('[Book1. xls]Sheet1:sheet30'!$A$1:$A$999)=DATE(2004,1,8))) Though this will work only, if the wokbook 'book1.xls' is opened!. If its closed, the above formula will return an erro (#REF) Frank ok, Frank Kabel's answer works, but the only thing that I didn't mention was that i'm not just looking through Sheet1, it's more like Sheet1 through Sheet30. I know that putting something like: Sheet1:Sheet30 means everything between Sheet1 thru Sheet30 (in my case, "my Sheet1" is named Top and "my Sheet30" is named Bottom) but when I put in "Top:Bottom'!" into the formula, it doesn't work. What am I to do? |
#9
|
|||
|
|||
Counting cells between 2 values
"abxy " wrote...
ok, Frank Kabel's answer works, but the only thing that I didn't mention was that i'm not just looking through Sheet1, it's more like Sheet1 through Sheet30. All the other respondents' formulas also work because (no big surprise) they're all basically the same. I know that putting something like: Sheet1:Sheet30 means everything between Sheet1 thru Sheet30 (in my case, "my Sheet1" is named Top and "my Sheet30" is named Bottom) but when I put in "Top:Bottom'!" into the formula, it doesn't work. You can't use 3D references in this situation. You need to create a list of the worksheet names to be processed. Best (as in most easily maintained) to enter such a list in a 30-row by 1-column range and give that range a name like WSLST. Then you need to use trickery. =SUMPRODUCT((N(OFFSET(INDIRECT("'"&WSLST&"'!A1"), {0;1;2;3;4;5;6;7;8;9},0,1,1))=DATEVALUE("1/1/2004")) *(N(OFFSET(INDIRECT("'"&WSLST&"'!A1"), {0;1;2;3;4;5;6;7;8;9},0,1,1))=DATEVALUE("1/8/2004"))) If the references would be to another workbook, then add the workbook's name. =SUMPRODUCT((N(OFFSET(INDIRECT("'[workbook.xls]"&WSLST&"'!A1"), {0;1;2;3;4;5;6;7;8;9},0,1,1))=DATEVALUE("1/1/2004")) *(N(OFFSET(INDIRECT("'[workbook.xls]"&WSLST&"'!A1"), {0;1;2;3;4;5;6;7;8;9},0,1,1))=DATEVALUE("1/8/2004"))) This requires the other workbook to be open. If the other workbook would be closed, there's no easy way to do this. All I can think of would involve calling a user-defined function that in turn calls the udf in the following linked article. http://www.google.com/groups?selm=sH...wsranger. com -- To top-post is human, to bottom-post and snip is sublime. |
#10
|
|||
|
|||
Counting cells between 2 values
wait, let me get this straight. it'll only work if the default
workbook, 'Book1.xsl' is open, meaning that it'll still work if my file 'Febuary.xls' is closed, it will still work? --- Message posted from http://www.ExcelForum.com/ Hi I do not know what file 'february.xls' is - you ddidn't mention this workbook before?? The important thing is, taht the referenced workbookname in the formula =SUMPRODUCT((THREED('[Book1.xls]Sheet1:sheet30'!$A$1:$A$999)=DATE(2004 ,1,1))*(THREED('[Book1. xls]Sheet1:sheet30'!$A$1:$A$999)=DATE(2004,1,8))) has to be opened. In this case I used the dummy name 'book1.xls' for this workbook. So you have to open the workbook which contains the values to count. Just try it by yourself. Frank |
|
Thread Tools | |
Display Modes | |
|
|