I am trying to create a running column array that counts the number of occurrences by person by week. It can't be a pivot table or separate array table but a calculated column that updates beside the raw data. I think I need a combination of an array, vlookup, and query, but I am unsure how to do it.

Here is an example of the sheet: https://docs.google.com/spreadsheets/d/1KtFZX4K2LmPqgm1PDQ28i2GcbrVEr4TYwzKjbQiZs_U/edit?usp=sharing

I've tried this solution: https://stackoverflow.com/questions/62814725/group-by-id-and-count-by-sub-unique-id-in-google-sheet-or-excel Creating a WeekNumber column and grouping the data that way. (The attempt is in the spreadsheet example).

Any thoughts on how to make this work?

  • This question was bumped by Community user. While links to external resources might be helpful, all the relevant details should be included in the question. Commented Sep 8, 2023 at 17:39

2 Answers 2


you can try to use:


but the results are different than the column 'desired_count', whose logic i couldn't really understand.


I was able to solve it by creating 2 helper columns: WeekNumber and a unique id column. From there I used array, query, and vlookup. It's not the most elegant solution but it works!

The solved function is in the original google sheet link under the FINAL column.

  • As mentioned in the comment added to the question, posts should be self-contained. In this case, all the relevant details that corresponds to the answer should be included in the answer. Commented Sep 8, 2023 at 17:41

Your Answer

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

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