How to count rows which show specified dates

Solved/Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I am trying to get help with stats.

I have a spreadsheet which shows the date a specific action was started in each row. I need to be able to count how many actions (rows) were started each week, but I have no idea how to manage this, I thought it would be COUNTIF, but none of the formulas I have tried worked.

Added to this - if I can ever sort it out.

I also need to count which actions (rows) were completed between specified dates

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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))
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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