How to count rows which show specified dates
Solved/Closed
sparker
-
Jan 7, 2010 at 06:20 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 8, 2010 at 06:51 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 8, 2010 at 06:51 AM
Related:
- How to count rows which show specified dates
- How to count names in excel - Guide
- Different dates of "end to end encryption message" on blank chat? ✓ - Network Forum
- How to delete a row in a table in word - Guide
- How to activate facebook dating - Guide
- Using nested if to blank out adjacent cells - Excel Forum
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 8, 2010 at 06:51 AM
Jan 8, 2010 at 06:51 AM
in my region excel dates are entered as m/d/yy and not as you entered.
your data is like this
date recd query date resolved wk num recd wk num reslvd
5/12/2009 a 7/27/2009 20 31
7/23/2009 s 7/27/2009 30 31
7/25/2009 d 7/31/2009 30 31
7/27/2009 f 7/28/2009 31 31
7/28/2009 g 7/29/2009 31 31
7/28/2009 h 7/29/2009 31 31
7/31/2009 k 8/10/2009 31 33
the formula in d2 is
=WEEKNUM(A2)
copy D2 down
formula in E2 is
=WEEKNUM(C2)
copy E2 down
now see the results
this from cell A9
rel dates recd same wk wk 30 to 31
7/27-7/31 4 3 2
in B10 the formula is
=COUNTIF(D2:D8,31)
in C10 the formula is
=SUMPRODUCT((D2:D8=31)*(E2:E8=31))
in D10 the formula is
=SUMPRODUCT((D2:D8=30)*(E2:E8=31))
your data is like this
date recd query date resolved wk num recd wk num reslvd
5/12/2009 a 7/27/2009 20 31
7/23/2009 s 7/27/2009 30 31
7/25/2009 d 7/31/2009 30 31
7/27/2009 f 7/28/2009 31 31
7/28/2009 g 7/29/2009 31 31
7/28/2009 h 7/29/2009 31 31
7/31/2009 k 8/10/2009 31 33
the formula in d2 is
=WEEKNUM(A2)
copy D2 down
formula in E2 is
=WEEKNUM(C2)
copy E2 down
now see the results
this from cell A9
rel dates recd same wk wk 30 to 31
7/27-7/31 4 3 2
in B10 the formula is
=COUNTIF(D2:D8,31)
in C10 the formula is
=SUMPRODUCT((D2:D8=31)*(E2:E8=31))
in D10 the formula is
=SUMPRODUCT((D2:D8=30)*(E2:E8=31))
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 7, 2010 at 08:17 PM
Jan 7, 2010 at 08:17 PM
Post a small extract of data if necessary with fictitious data due to security reasons
Date How Received Query Date Resolved
12/05/2009 E-mail Invoice for SG 27/07/2009
23/07/2009 E-mail Invoice for WHK 27/07/2009
25/07/2009 Phone Tax refund 31/07/2009
27/07/2009 E-mail Fee Invoice 28/07/2009
28/07/2009 E-mail Fee Invoice 29/07/2009
28/07/2009 E-mail AA to change pens 29/07/2009
31/07/2009 E-mail AA to change pens 10/08/2009
Out of the information above what I want to be able to see in the end is that:
how many queries were received in the week 27/7/2009 - 31/7/2009 ie 4
how many queries of that week were resolved in that week ie 3
how many queries of previous weeks were resolved in that week ie 3
If you could help that would be brilliant
12/05/2009 E-mail Invoice for SG 27/07/2009
23/07/2009 E-mail Invoice for WHK 27/07/2009
25/07/2009 Phone Tax refund 31/07/2009
27/07/2009 E-mail Fee Invoice 28/07/2009
28/07/2009 E-mail Fee Invoice 29/07/2009
28/07/2009 E-mail AA to change pens 29/07/2009
31/07/2009 E-mail AA to change pens 10/08/2009
Out of the information above what I want to be able to see in the end is that:
how many queries were received in the week 27/7/2009 - 31/7/2009 ie 4
how many queries of that week were resolved in that week ie 3
how many queries of previous weeks were resolved in that week ie 3
If you could help that would be brilliant
