Excel - How to count rows which show specified date?

October 2017





Issue

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
.

Solution

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))

Note

Thanks to venkat1926 for this tip on the forum.
Published by aakai1056. Latest update on November 6, 2016 at 03:58 PM by owilson.
This document, titled "Excel - How to count rows which show specified date?," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).