# Leads per Week For Last 10 Weeks Formula With Countif

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
``````
• I think that does count back 10 weeks but it just averages the number of leads over those 10 weeks. I need the exact number of leads each week. Commented Apr 16, 2014 at 0:29
• Why don't you share a doc with us and show us the expected result? Commented Apr 16, 2014 at 6:41

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.

• Thanks pnuts, this works for me. I just had to replace weeknum(A1) with LEFT(TEXT(A1; "w d"); 2). I believe weeknum doesn't yet work in Google spreadsheets. Commented Apr 17, 2014 at 20:15