Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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