Google Sheets – Leads per Week Formula with COUNTIF

google sheets

I have a long list of dates (like below) that indicate Leads and I am trying to get number of leads per week for the last 10 weeks. I know I can get the current week number with MID(TEXT(Today(),"yyww"), 3, 2). I’ve researched and think countif and arrayformula might get me there but I don’t know how to apply them. Countif the week that a date corresponds to equals the current week, and the year is the current year. Seems simple enough but I can't quite figure out this countif.

These are the dates:

12/30/2013
12/9/2013
12/12/2013
12/23/2013
12/30/2013
12/5/2013
1/2/2014
1/2/2014
1/2/2014
1/6/2014
1/6/2014

Best Answer

Perhaps in a helper column (say B assuming data is in A): 

=if(year(A1)=year(now()),weeknum(A1),"")

to identify entries in the latest 10 weeks (by week number) by row and a table in rows1:10 of: 

=weeknum(now())-10+ROW()

for one column (say E) and: 

=countif(B:B,E1)

for another, with all formulae copied down to suit.

Related Topic