count between two date with one more condition

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data like following

WO createdate grp1
1 20/02/2007 a
2 21/02/2007 a
3 23/02/2007 a
4 24/02/2007 a
5 25/02/2007 a
6 26/02/2007 a
7 20/02/2007 b
8 21/02/2007 b
9 23/02/2007 b
10 24/02/2007 b
11 25/02/2007 b
12 26/02/2007 b


I want to calcutale total no of records for each group between Specific Date


Like this:

20/02/2007 -23/02/2007 23/02/2007- 26-02/2007
a 3 3
b 3 3

Can Anybody help its urgent
 
Assuming dates are in B2:B13 and "a" and "b" are in C2:C13, try:

=SUMPRODUCT(--(B2:B13>=DATE(2007,2,20)),
--(B2:B13<=DATE(2007,2,23)),--(C2:C13="a"))

=SUMPRODUCT(--(B2:B13>=DATE(2007,2,20)),
--(B2:B13<=DATE(2007,2,23)),--(C2:C13="b"))


Of course, you could use cell references for the following items:
DATE(2007,2,20)
DATE(2007,2,23)
"a"
"b"
 
Back
Top