Google-sheets – Count related to date items in a Google spreadsheet

google sheets

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.

Best Answer

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

Related Topic