-1

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?

Improve this question
1
  • 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.
    – Rubén ♦
    Sep 8 at 17:39

2 Answers 2

Reset to default
0

you can try to use:

=countifs($B$2:$B$10,B2,$A$2:$A$10,A2)

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

Improve this answer
-1

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.

Improve this answer
1
  • 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.
    – Rubén ♦
    Sep 8 at 17:41

Your Answer

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

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