| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting number of observations within a date range?
Hi,
I have two columns with dates in my dataset, which define the start and end dates for a certain event. For each observation in the dataset, I'd like to count the number of other observations in the dataset that overlap with this event window. I.e. Event Start date End date Simultaneous events ----------------------------------------------------------------------------------- XYZ 11jan04 07feb04 ??? ZZZ 11jan04 08feb04 ??? YYY 14jan04 22jan04 ??? XXX 21jan04 13mar04 ??? ZZY 30jan04 04feb04 ??? aso.aso. Many thanks for your help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of observations within a date range?
If I understand your requirement:
in D3: (I have assumed data starts in row 2 so change 2 to whatever in formula below): =SUMPRODUCT(--($B$2:$B$7=B2),--($C$2:$C$7<=C2)) Copy down as required HTH "Nic" wrote: Hi, I have two columns with dates in my dataset, which define the start and end dates for a certain event. For each observation in the dataset, I'd like to count the number of other observations in the dataset that overlap with this event window. I.e. Event Start date End date Simultaneous events ----------------------------------------------------------------------------------- XYZ 11jan04 07feb04 ??? ZZZ 11jan04 08feb04 ??? YYY 14jan04 22jan04 ??? XXX 21jan04 13mar04 ??? ZZY 30jan04 04feb04 ??? aso.aso. Many thanks for your help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of observations within a date range?
...... I am wrong .. need to re-think!
"Toppers" wrote: If I understand your requirement: in D3: (I have assumed data starts in row 2 so change 2 to whatever in formula below): =SUMPRODUCT(--($B$2:$B$7=B2),--($C$2:$C$7<=C2)) Copy down as required HTH "Nic" wrote: Hi, I have two columns with dates in my dataset, which define the start and end dates for a certain event. For each observation in the dataset, I'd like to count the number of other observations in the dataset that overlap with this event window. I.e. Event Start date End date Simultaneous events ----------------------------------------------------------------------------------- XYZ 11jan04 07feb04 ??? ZZZ 11jan04 08feb04 ??? YYY 14jan04 22jan04 ??? XXX 21jan04 13mar04 ??? ZZY 30jan04 04feb04 ??? aso.aso. Many thanks for your help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of observations within a date range?
Yes, understood correctly. To specify again, the columns and rows are as
follows: A B C D ---------------------------------------------- 1 XYZ 11jan04 07feb04 ??? 2 ZZZ 11jan04 08feb04 ??? 3 YYY 14jan04 22jan04 ??? 4 XXX 21jan04 13mar04 ??? 5 ZZY 30jan04 04feb04 ??? and so on. "Toppers" wrote: If I understand your requirement: in D3: (I have assumed data starts in row 2 so change 2 to whatever in formula below): =SUMPRODUCT(--($B$2:$B$7=B2),--($C$2:$C$7<=C2)) Copy down as required HTH "Nic" wrote: Hi, I have two columns with dates in my dataset, which define the start and end dates for a certain event. For each observation in the dataset, I'd like to count the number of other observations in the dataset that overlap with this event window. I.e. Event Start date End date Simultaneous events ----------------------------------------------------------------------------------- XYZ 11jan04 07feb04 ??? ZZZ 11jan04 08feb04 ??? YYY 14jan04 22jan04 ??? XXX 21jan04 13mar04 ??? ZZY 30jan04 04feb04 ??? aso.aso. Many thanks for your help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of observations within a date range?
Nic,
With your starting dates in column B, starting in row 2, and ending dates in column C =SUMPRODUCT((((B2<=$C$2:$C$1000)*(C2=$C$2:$C$1000 )+(C2=$B$2:$B$1000)*(C2<=$C$2:$C$1000))0)*1)-1 HTH, Bernie MS Excel MVP "Nic" wrote in message ... Hi, I have two columns with dates in my dataset, which define the start and end dates for a certain event. For each observation in the dataset, I'd like to count the number of other observations in the dataset that overlap with this event window. I.e. Event Start date End date Simultaneous events ----------------------------------------------------------------------------------- XYZ 11jan04 07feb04 ??? ZZZ 11jan04 08feb04 ??? YYY 14jan04 22jan04 ??? XXX 21jan04 13mar04 ??? ZZY 30jan04 04feb04 ??? aso.aso. Many thanks for your help! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of observations within a date range?
Thanks Bernie.
Something must be wrong, I get a lot of negative values? "Bernie Deitrick" wrote: Nic, With your starting dates in column B, starting in row 2, and ending dates in column C =SUMPRODUCT((((B2<=$C$2:$C$1000)*(C2=$C$2:$C$1000 )+(C2=$B$2:$B$1000)*(C2<=$C$2:$C$1000))0)*1)-1 HTH, Bernie MS Excel MVP "Nic" wrote in message ... Hi, I have two columns with dates in my dataset, which define the start and end dates for a certain event. For each observation in the dataset, I'd like to count the number of other observations in the dataset that overlap with this event window. I.e. Event Start date End date Simultaneous events ----------------------------------------------------------------------------------- XYZ 11jan04 07feb04 ??? ZZZ 11jan04 08feb04 ??? YYY 14jan04 22jan04 ??? XXX 21jan04 13mar04 ??? ZZY 30jan04 04feb04 ??? aso.aso. Many thanks for your help! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of observations within a date range?
Nic,
Two things: the formula cannot produce negative numbers, and you need to take off the -1: I was counting the number of OTHER programs open, not including the project on the current line (my bad). Anyway, if you post your email here or contact me privately, I will send you a working example workbook. HTH, Bernie MS Excel MVP "Nic" wrote in message ... Thanks Bernie. Something must be wrong, I get a lot of negative values? "Bernie Deitrick" wrote: Nic, With your starting dates in column B, starting in row 2, and ending dates in column C =SUMPRODUCT((((B2<=$C$2:$C$1000)*(C2=$C$2:$C$1000 )+(C2=$B$2:$B$1000)*(C2<=$C$2:$C$1000))0)*1)-1 HTH, Bernie MS Excel MVP "Nic" wrote in message ... Hi, I have two columns with dates in my dataset, which define the start and end dates for a certain event. For each observation in the dataset, I'd like to count the number of other observations in the dataset that overlap with this event window. I.e. Event Start date End date Simultaneous events ----------------------------------------------------------------------------------- XYZ 11jan04 07feb04 ??? ZZZ 11jan04 08feb04 ??? YYY 14jan04 22jan04 ??? XXX 21jan04 13mar04 ??? ZZY 30jan04 04feb04 ??? aso.aso. Many thanks for your help! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of observations within a date range?
Sorry, by bad! I must have inserted the original formula you gave wrong into
my sheet. It's working now, thanks a lot you your help! - Nic "Bernie Deitrick" wrote: Nic, Two things: the formula cannot produce negative numbers, and you need to take off the -1: I was counting the number of OTHER programs open, not including the project on the current line (my bad). Anyway, if you post your email here or contact me privately, I will send you a working example workbook. HTH, Bernie MS Excel MVP "Nic" wrote in message ... Thanks Bernie. Something must be wrong, I get a lot of negative values? "Bernie Deitrick" wrote: Nic, With your starting dates in column B, starting in row 2, and ending dates in column C =SUMPRODUCT((((B2<=$C$2:$C$1000)*(C2=$C$2:$C$1000 )+(C2=$B$2:$B$1000)*(C2<=$C$2:$C$1000))0)*1)-1 HTH, Bernie MS Excel MVP "Nic" wrote in message ... Hi, I have two columns with dates in my dataset, which define the start and end dates for a certain event. For each observation in the dataset, I'd like to count the number of other observations in the dataset that overlap with this event window. I.e. Event Start date End date Simultaneous events ----------------------------------------------------------------------------------- XYZ 11jan04 07feb04 ??? ZZZ 11jan04 08feb04 ??? YYY 14jan04 22jan04 ??? XXX 21jan04 13mar04 ??? ZZY 30jan04 04feb04 ??? aso.aso. Many thanks for your help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting if between date range | Excel Worksheet Functions | |||
| Counting number of instances in a range | Excel Worksheet Functions | |||
| Counting unique text/number cells from a range | Excel Worksheet Functions | |||
| Number of Observations | Excel Discussion (Misc queries) | |||
| Counting values within a Date Range | Excel Discussion (Misc queries) | |||
Linear Mode
