3

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
Improve this question
2
  • 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.
    – user54941
    Apr 16, 2014 at 0:29
  • Why don't you share a doc with us and show us the expected result?
    – Jacob Jan Tuinstra
    Apr 16, 2014 at 6:41

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • 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.
    – user54941
    Apr 17, 2014 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.