| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting if Date1 is after and Date2 is before another date
First of all, apologies for putting this in a new question
If i have the following dates A B C D E F 1 04/01/2005 17/02/2005 03/04/2005 03/07/2005 3/10/2005 03/01/2006 2 04/01/2005 18/02/2005 * Ans 1 * Ans 2 * Ans 3 *Ans 4 3 05/01/2005 18/02/2005 4 06/01/2005 19/02/2005 5 07/01/2005 20/02/2005 What formula need to be written in cells C2:C5 (*Ans 1 - 4) to give * Ans 1 - Count if A2:A5 is greater than B1 AND B2:B5 is less than C1 * Ans 2 - Count if A2:A5 is greater than B1 AND B2:B5 is less than D1 * Ans 3 - Count if A2:A5 is greater than B1 AND B2:B5 is less than E1 * Ans 4 - Count if A2:A5 is greater than B1 AND B2:B5 is less than F1 Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting if Date1 is after and Date2 is before another date
=SUMPRODUCT(($A$2:$A$5$B$1)*($B$2:$B$5<C$1)) in C2, and copy across.
I assume that you do want the answers in C2:F2, as shown in your example, not in C2:C5 as described. -- David Biddulph "Mally" wrote in message ... First of all, apologies for putting this in a new question If i have the following dates A B C D E F 1 04/01/2005 17/02/2005 03/04/2005 03/07/2005 3/10/2005 03/01/2006 2 04/01/2005 18/02/2005 * Ans 1 * Ans 2 * Ans 3 *Ans 4 3 05/01/2005 18/02/2005 4 06/01/2005 19/02/2005 5 07/01/2005 20/02/2005 What formula need to be written in cells C2:C5 (*Ans 1 - 4) to give * Ans 1 - Count if A2:A5 is greater than B1 AND B2:B5 is less than C1 * Ans 2 - Count if A2:A5 is greater than B1 AND B2:B5 is less than D1 * Ans 3 - Count if A2:A5 is greater than B1 AND B2:B5 is less than E1 * Ans 4 - Count if A2:A5 is greater than B1 AND B2:B5 is less than F1 Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting if Date1 is after and Date2 is before another date
Hi,
This should give you ans1 and I think you should be able to work out the rest =SUMPRODUCT((A2:A5B1)*(B2:B5<C1)) Mike "Mally" wrote: First of all, apologies for putting this in a new question If i have the following dates A B C D E F 1 04/01/2005 17/02/2005 03/04/2005 03/07/2005 3/10/2005 03/01/2006 2 04/01/2005 18/02/2005 * Ans 1 * Ans 2 * Ans 3 *Ans 4 3 05/01/2005 18/02/2005 4 06/01/2005 19/02/2005 5 07/01/2005 20/02/2005 What formula need to be written in cells C2:C5 (*Ans 1 - 4) to give * Ans 1 - Count if A2:A5 is greater than B1 AND B2:B5 is less than C1 * Ans 2 - Count if A2:A5 is greater than B1 AND B2:B5 is less than D1 * Ans 3 - Count if A2:A5 is greater than B1 AND B2:B5 is less than E1 * Ans 4 - Count if A2:A5 is greater than B1 AND B2:B5 is less than F1 Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting if Date1 is after and Date2 is before another date
Thanks to both of you. It works great!
"David Biddulph" wrote: =SUMPRODUCT(($A$2:$A$5$B$1)*($B$2:$B$5<C$1)) in C2, and copy across. I assume that you do want the answers in C2:F2, as shown in your example, not in C2:C5 as described. -- David Biddulph "Mally" wrote in message ... First of all, apologies for putting this in a new question If i have the following dates A B C D E F 1 04/01/2005 17/02/2005 03/04/2005 03/07/2005 3/10/2005 03/01/2006 2 04/01/2005 18/02/2005 * Ans 1 * Ans 2 * Ans 3 *Ans 4 3 05/01/2005 18/02/2005 4 06/01/2005 19/02/2005 5 07/01/2005 20/02/2005 What formula need to be written in cells C2:C5 (*Ans 1 - 4) to give * Ans 1 - Count if A2:A5 is greater than B1 AND B2:B5 is less than C1 * Ans 2 - Count if A2:A5 is greater than B1 AND B2:B5 is less than D1 * Ans 3 - Count if A2:A5 is greater than B1 AND B2:B5 is less than E1 * Ans 4 - Count if A2:A5 is greater than B1 AND B2:B5 is less than F1 Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting if Date1 is after and Date2 is before another date
In the example below how could I get the formula to change automatically so
that it only uses data where the numbers in column A match. If there is only one number that matches (A6) then this data will not be used. e.g. Cell D2:G2 will use only the data in B1:C3 Cell D4:G4 will use only the data in B4:C5 Cell D6:G6 will not display any data The answers will be displayed in the table when I copy the formula down the sheet (The ones below are not correct, I entered any numbers) A B C D E F G 1 1 04/01/2005 17/02/2005 03/04/2005 03/07/2005 3/10/2005 03/01/2006 2 1 04/01/2005 18/02/2005 1 2 2 3 3 1 05/01/2005 18/02/2005 4 2 06/01/2005 19/02/2005 2 2 2 3 5 2 07/01/2005 20/02/2005 6 3 08/01/2005 25/02/2005 "David Biddulph" wrote: =SUMPRODUCT(($A$2:$A$5$B$1)*($B$2:$B$5<C$1)) in C2, and copy across. I assume that you do want the answers in C2:F2, as shown in your example, not in C2:C5 as described. -- David Biddulph "Mally" wrote in message ... First of all, apologies for putting this in a new question If i have the following dates A B C D E F 1 04/01/2005 17/02/2005 03/04/2005 03/07/2005 3/10/2005 03/01/2006 2 04/01/2005 18/02/2005 * Ans 1 * Ans 2 * Ans 3 *Ans 4 3 05/01/2005 18/02/2005 4 06/01/2005 19/02/2005 5 07/01/2005 20/02/2005 What formula need to be written in cells C2:C5 (*Ans 1 - 4) to give * Ans 1 - Count if A2:A5 is greater than B1 AND B2:B5 is less than C1 * Ans 2 - Count if A2:A5 is greater than B1 AND B2:B5 is less than D1 * Ans 3 - Count if A2:A5 is greater than B1 AND B2:B5 is less than E1 * Ans 4 - Count if A2:A5 is greater than B1 AND B2:B5 is less than F1 Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting if Date1 is after and Date2 is before another date
A bit clearer!
In the example below how could I get the formula to change automatically so that it only uses data where the numbers in column A match. If there is only one number that matches (A6) then this data will not be used. e.g. Cell D2:G2 will use only the data in B1:C3 Cell D4:G4 will use only the data in B4:C5 Cell D6:G6 will not display any data The answers will be displayed in the table when I copy the formula down the sheet (The ones below are not correct, I entered any numbers) A B C D E F G 1 1 Date Date Date Date Date Date 2 1 Date Date 1 2 2 3 3 1 Date Date 4 2 Date Date 2 2 2 3 5 2 Date Date 6 3 Date Date "Mally" wrote: In the example below how could I get the formula to change automatically so that it only uses data where the numbers in column A match. If there is only one number that matches (A6) then this data will not be used. e.g. Cell D2:G2 will use only the data in B1:C3 Cell D4:G4 will use only the data in B4:C5 Cell D6:G6 will not display any data The answers will be displayed in the table when I copy the formula down the sheet (The ones below are not correct, I entered any numbers) A B C D E F G 1 1 04/01/2005 17/02/2005 03/04/2005 03/07/2005 3/10/2005 03/01/2006 2 1 04/01/2005 18/02/2005 1 2 2 3 3 1 05/01/2005 18/02/2005 4 2 06/01/2005 19/02/2005 2 2 2 3 5 2 07/01/2005 20/02/2005 6 3 08/01/2005 25/02/2005 "David Biddulph" wrote: =SUMPRODUCT(($A$2:$A$5$B$1)*($B$2:$B$5<C$1)) in C2, and copy across. I assume that you do want the answers in C2:F2, as shown in your example, not in C2:C5 as described. -- David Biddulph "Mally" wrote in message ... First of all, apologies for putting this in a new question If i have the following dates A B C D E F 1 04/01/2005 17/02/2005 03/04/2005 03/07/2005 3/10/2005 03/01/2006 2 04/01/2005 18/02/2005 * Ans 1 * Ans 2 * Ans 3 *Ans 4 3 05/01/2005 18/02/2005 4 06/01/2005 19/02/2005 5 07/01/2005 20/02/2005 What formula need to be written in cells C2:C5 (*Ans 1 - 4) to give * Ans 1 - Count if A2:A5 is greater than B1 AND B2:B5 is less than C1 * Ans 2 - Count if A2:A5 is greater than B1 AND B2:B5 is less than D1 * Ans 3 - Count if A2:A5 is greater than B1 AND B2:B5 is less than E1 * Ans 4 - Count if A2:A5 is greater than B1 AND B2:B5 is less than F1 Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Name The Day Of The Date2 | Excel Discussion (Misc queries) | |||
| Counting Date Changes | Excel Discussion (Misc queries) | |||
| Counting Date Changes | Excel Discussion (Misc queries) | |||
| date counting | Excel Worksheet Functions | |||
| excel formula counting date to date in 4 columns | Excel Worksheet Functions | |||
Linear Mode
