| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count between dates
I have dates in column A and names in column B. I need to count how many
names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count between dates
Here's how I'd do it, Vicki: Create a helper column, say in N,
"=AND(A2=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE) and it'll count how many rows have dates in that range. Do you need to eliminate duplicate names? --- "Vicki Leibowitz" wrote: I have dates in column A and names in column B. I need to count how many names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count between dates
Hi Bob.
Basically I have named the date range as "Date" and the name range as "Names". I have used another sheet for my formulas and have 2010/01/01 in cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts). I tried your formula but it comes back as FALSE? Surely if I can sumproduct between dates there must be a way to countproduct? I don't need to eliminate any duplicates. I am a new excel learner. Your assistance is much appreciated. Thanks, Vicki "Bob Bridges" wrote: Here's how I'd do it, Vicki: Create a helper column, say in N, "=AND(A2=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE) and it'll count how many rows have dates in that range. Do you need to eliminate duplicate names? --- "Vicki Leibowitz" wrote: I have dates in column A and names in column B. I need to count how many names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count between dates
Hi,
=SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) Where J1 and k1 are you first and last dates -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Vicki Leibowitz" wrote: Hi Bob. Basically I have named the date range as "Date" and the name range as "Names". I have used another sheet for my formulas and have 2010/01/01 in cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts). I tried your formula but it comes back as FALSE? Surely if I can sumproduct between dates there must be a way to countproduct? I don't need to eliminate any duplicates. I am a new excel learner. Your assistance is much appreciated. Thanks, Vicki "Bob Bridges" wrote: Here's how I'd do it, Vicki: Create a helper column, say in N, "=AND(A2=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE) and it'll count how many rows have dates in that range. Do you need to eliminate duplicate names? --- "Vicki Leibowitz" wrote: I have dates in column A and names in column B. I need to count how many names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count between dates
Hi,
I've tried everything. =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) gives me #NA. Am I just being a complete idiot? I'm sure feeling like one. "Mike H" wrote: Hi, =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) Where J1 and k1 are you first and last dates -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Vicki Leibowitz" wrote: Hi Bob. Basically I have named the date range as "Date" and the name range as "Names". I have used another sheet for my formulas and have 2010/01/01 in cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts). I tried your formula but it comes back as FALSE? Surely if I can sumproduct between dates there must be a way to countproduct? I don't need to eliminate any duplicates. I am a new excel learner. Your assistance is much appreciated. Thanks, Vicki "Bob Bridges" wrote: Here's how I'd do it, Vicki: Create a helper column, say in N, "=AND(A2=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE) and it'll count how many rows have dates in that range. Do you need to eliminate duplicate names? --- "Vicki Leibowitz" wrote: I have dates in column A and names in column B. I need to count how many names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count between dates
That suggests that you've got the #N/A error in at least one of the cells
which are feeding into the formula. Mike did, of course, mean "Where J7 and K7 are you first and last dates", rather than J1 and K1 (or he meant to change the J7 and K7 references in the formula to J1 and K1), but that would not give an #N/A error so you need to look at your input data values. -- David Biddulph "Vicki Leibowitz" wrote in message ... Hi, I've tried everything. =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) gives me #NA. Am I just being a complete idiot? I'm sure feeling like one. "Mike H" wrote: Hi, =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) Where J1 and k1 are you first and last dates -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Vicki Leibowitz" wrote: Hi Bob. Basically I have named the date range as "Date" and the name range as "Names". I have used another sheet for my formulas and have 2010/01/01 in cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts). I tried your formula but it comes back as FALSE? Surely if I can sumproduct between dates there must be a way to countproduct? I don't need to eliminate any duplicates. I am a new excel learner. Your assistance is much appreciated. Thanks, Vicki "Bob Bridges" wrote: Here's how I'd do it, Vicki: Create a helper column, say in N, "=AND(A2=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE) and it'll count how many rows have dates in that range. Do you need to eliminate duplicate names? --- "Vicki Leibowitz" wrote: I have dates in column A and names in column B. I need to count how many names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count between dates
Hi all,
Thanks for assistance. I started from scratch and managed to get the answers using the sumproduct formula: =SUMPRODUCT((Date=B2)*(Date<=B3)) So basic I could kick myself! Thanks again. "Vicki Leibowitz" wrote: Hi, I've tried everything. =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) gives me #NA. Am I just being a complete idiot? I'm sure feeling like one. "Mike H" wrote: Hi, =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) Where J1 and k1 are you first and last dates -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Vicki Leibowitz" wrote: Hi Bob. Basically I have named the date range as "Date" and the name range as "Names". I have used another sheet for my formulas and have 2010/01/01 in cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts). I tried your formula but it comes back as FALSE? Surely if I can sumproduct between dates there must be a way to countproduct? I don't need to eliminate any duplicates. I am a new excel learner. Your assistance is much appreciated. Thanks, Vicki "Bob Bridges" wrote: Here's how I'd do it, Vicki: Create a helper column, say in N, "=AND(A2=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE) and it'll count how many rows have dates in that range. Do you need to eliminate duplicate names? --- "Vicki Leibowitz" wrote: I have dates in column A and names in column B. I need to count how many names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count between dates
I gathered thanks.
I figured it out - the formula turned out to be so basic! I should start learning what the different error messages mean. Thank you. "David Biddulph" wrote: That suggests that you've got the #N/A error in at least one of the cells which are feeding into the formula. Mike did, of course, mean "Where J7 and K7 are you first and last dates", rather than J1 and K1 (or he meant to change the J7 and K7 references in the formula to J1 and K1), but that would not give an #N/A error so you need to look at your input data values. -- David Biddulph "Vicki Leibowitz" wrote in message ... Hi, I've tried everything. =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) gives me #NA. Am I just being a complete idiot? I'm sure feeling like one. "Mike H" wrote: Hi, =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) Where J1 and k1 are you first and last dates -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Vicki Leibowitz" wrote: Hi Bob. Basically I have named the date range as "Date" and the name range as "Names". I have used another sheet for my formulas and have 2010/01/01 in cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts). I tried your formula but it comes back as FALSE? Surely if I can sumproduct between dates there must be a way to countproduct? I don't need to eliminate any duplicates. I am a new excel learner. Your assistance is much appreciated. Thanks, Vicki "Bob Bridges" wrote: Here's how I'd do it, Vicki: Create a helper column, say in N, "=AND(A2=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE) and it'll count how many rows have dates in that range. Do you need to eliminate duplicate names? --- "Vicki Leibowitz" wrote: I have dates in column A and names in column B. I need to count how many names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count between dates
Thanks for spotting my error David.
Vixter, The formula =SUMPRODUCT((Date=B2)*(Date<=B3)) will corectly count dates in a range but based upon the original post it doesn't answer the question. I have dates in column A and names in column B. What if column B has blanks in? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Vixter" wrote: I gathered thanks. I figured it out - the formula turned out to be so basic! I should start learning what the different error messages mean. Thank you. "David Biddulph" wrote: That suggests that you've got the #N/A error in at least one of the cells which are feeding into the formula. Mike did, of course, mean "Where J7 and K7 are you first and last dates", rather than J1 and K1 (or he meant to change the J7 and K7 references in the formula to J1 and K1), but that would not give an #N/A error so you need to look at your input data values. -- David Biddulph "Vicki Leibowitz" wrote in message ... Hi, I've tried everything. =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) gives me #NA. Am I just being a complete idiot? I'm sure feeling like one. "Mike H" wrote: Hi, =SUMPRODUCT((A1:A30=J7)*(A1:A30<=K7)*(B1:B30<"") ) Where J1 and k1 are you first and last dates -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Vicki Leibowitz" wrote: Hi Bob. Basically I have named the date range as "Date" and the name range as "Names". I have used another sheet for my formulas and have 2010/01/01 in cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts). I tried your formula but it comes back as FALSE? Surely if I can sumproduct between dates there must be a way to countproduct? I don't need to eliminate any duplicates. I am a new excel learner. Your assistance is much appreciated. Thanks, Vicki "Bob Bridges" wrote: Here's how I'd do it, Vicki: Create a helper column, say in N, "=AND(A2=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE) and it'll count how many rows have dates in that range. Do you need to eliminate duplicate names? --- "Vicki Leibowitz" wrote: I have dates in column A and names in column B. I need to count how many names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Dates between Dates exclude Text | Excel Discussion (Misc queries) | |||
| I want to count the total Number of dates between two dates How? | Excel Worksheet Functions | |||
| count between two dates | Excel Discussion (Misc queries) | |||
| Count of Dates | Excel Discussion (Misc queries) | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Linear Mode
