Count related to date items in a Google spreadsheet

I have a spreadsheet that shows how many are still completing a task at two and 8 weeks.

• Col A Name
• Col B has Start a date
• Col C is the WeekNumber of the date from col A (Using WeekNumber Script)
• Col D has Yes if the task has not been completed in two weeks
• Col E has Yes if the task has not been completed in 8 weeks
• Col F Is a list of week numbers
• Col G Gives me a count of how many start the task in a particular week taken from the week number.
• Col H I need this to be the count of people still completing the task in 2 weeks
• Col I I need this to be the count of people still completing the task in 8 weeks

I am stumped on how to get the count for col I and H.

In the example sheet, RESULT, I added the following formula to cell G3:

=ARRAYFORMULA(COUNTIF(C3:C;FILTER(F3:F;F3:F<>"")))

In the next two columns I added these formulas:

Column H

=IF(COUNT(FILTER(\$C\$3:C;\$C\$3:C=F3;\$D\$3:D="Yes"))=0;"";
COUNT(FILTER(\$C\$3:C;\$C\$3:C=F3;\$D\$3:D="Yes")))

Column I

=IF(COUNT(FILTER(\$C\$3:C;\$C\$3:C=F3;\$E\$3:E="Yes"))=0;"";
COUNT(FILTER(\$C\$3:C;\$C\$3:C=F3;\$E\$3:E="Yes")))

Copy down the formulas, to cover the whole year.
See example file I've prepared: EXPECTED vs. RESULT

• Thanks for your reply - I probably did not explain my problem very well. What I am looking for is away of counting those who started in a particular week still not completed at 2 weeks. For instance if 7 people started in week 5 and only one has completed then I would expect answer to be 6 for that week No. Not a general count but one specific to each start week. – user33789 Mar 23 '13 at 16:09
• @user33789 See my example again. I've used the FREQUENCY formula, amongst other formulas. If you like it, I will re-change my answer. – Jacob Jan Tuinstra Mar 23 '13 at 21:00
• I have added another sheet to your example to show my problem better. It is the final 2 columns that are causing me the problem. I have put in the figures I would expect in those 2 columns. Which are the number of yeses for a particular week not just the overall number of yeses. I hope that is clear - Thanks for your help. – user33789 Mar 25 '13 at 8:24
• @user33789 Take a look at what I have accomplished. Column H has an ARRAYFORMULA and I and J have a FILTER and COUNT. I wanted to have an ARRAYFORMULA for the other columns as well, but this solution works !! – Jacob Jan Tuinstra Mar 27 '13 at 20:53
• Thank You that works a treat and is just what I was looking for . – user33789 Mar 31 '13 at 6:57