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 a array, vlookup, and query but am not sure 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 By creating a WeekNumber column and grouping the data that way. (Attempt is in the spreadsheet example).

Any thoughts on how to make this work?


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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