| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
count date occurances in range of dates...
Hi
I am stuck trying to come up with a formula to count the number of times dates occur within a range of dates. What I have is: col A has a range of dates, say A1:A100 col b with another set of dates B1:B7 What I need to do is return the number of times any of the individual dates in the B1:B7 range occur in A1:A100. e.g A B 10 Feb 05 26 Mar 05 15 Feb 05 29 Apr 05 26 Mar 05 10 Feb 05 29 Apr 05 06 May 05 11 Jan 05 18 Dec 05 The answer I'm looking for here would be 3. Each date in col B occurs once in col A. Getting very frustrated with this and can't seem to find an answer for it!! Many thanks in advance Alex |
|
#2
|
|||
|
|||
|
=SUMPRODUCT(COUNTIF(A1:A7,B1:B3))
-- HTH RP (remove nothere from the email address if mailing direct) "Alex" wrote in message ... Hi I am stuck trying to come up with a formula to count the number of times dates occur within a range of dates. What I have is: col A has a range of dates, say A1:A100 col b with another set of dates B1:B7 What I need to do is return the number of times any of the individual dates in the B1:B7 range occur in A1:A100. e.g A B 10 Feb 05 26 Mar 05 15 Feb 05 29 Apr 05 26 Mar 05 10 Feb 05 29 Apr 05 06 May 05 11 Jan 05 18 Dec 05 The answer I'm looking for here would be 3. Each date in col B occurs once in col A. Getting very frustrated with this and can't seem to find an answer for it!! Many thanks in advance Alex |
|
#3
|
|||
|
|||
|
Thanks for the speedy answer. Works great.
Cheers, Alex. "Bob Phillips" wrote: =SUMPRODUCT(COUNTIF(A1:A7,B1:B3)) -- HTH RP (remove nothere from the email address if mailing direct) "Alex" wrote in message ... Hi I am stuck trying to come up with a formula to count the number of times dates occur within a range of dates. What I have is: col A has a range of dates, say A1:A100 col b with another set of dates B1:B7 What I need to do is return the number of times any of the individual dates in the B1:B7 range occur in A1:A100. e.g A B 10 Feb 05 26 Mar 05 15 Feb 05 29 Apr 05 26 Mar 05 10 Feb 05 29 Apr 05 06 May 05 11 Jan 05 18 Dec 05 The answer I'm looking for here would be 3. Each date in col B occurs once in col A. Getting very frustrated with this and can't seem to find an answer for it!! Many thanks in advance Alex |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
| How do I get an IF statement to pull a date range?? | Excel Worksheet Functions | |||
| Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
| adding occurrences for date range | Excel Discussion (Misc queries) | |||
| Count the number of dates in a range that are earlier than today. | Excel Worksheet Functions | |||
Linear Mode
