Counting if Date1 is after and Date2 is before another date

[Mally]

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

 

[David Biddulph]

=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.

 

[Mike H]

Hi,

This should give you ans1 and I think you should be able to work out the rest

=SUMPRODUCT((A2:A5>B1)*(B2:B5<C1))

Mike

 

[Mally]

Thanks to both of you. It works great!

=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.

 

[Mally]

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

 

[Mally]

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

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