Running count of unique dates based on user

Question

I'm attempting to get a running count of unique dates based on user (to categorize how experienced the user was for that row) however I can't seem to figure it out. I've tried a variety of countifs and countuniqueifs but keep getting an error - I would like to it to count unique dates less than or equal to the date listed in that row, if that makes any sense. Words are hard. Here's a link. Can anyone help?

https://docs.google.com/spreadsheets/d/1aPM9YAo8qstmYov3-5v6DOG145z1qaURgIxMuPWVitw/edit?usp=sharing

Answer 1

Please see the newly added sheet ("Erik Help") which contains this formula:

=ArrayFormula({"unique_count";IF(B3:B="",,VLOOKUP(B3:B&C3:C,{UNIQUE(FILTER(B3:B&C3:C,B3:B<>"")),COUNTIFS(QUERY(UNIQUE(FILTER(B3:C,B3:B<>"")),"Select Col1"),QUERY(UNIQUE(FILTER(B3:C,B3:B<>"")),"Select Col1"),QUERY(UNIQUE(FILTER(B3:C,B3:B<>"")),"Select Col2"),"<="&QUERY(UNIQUE(FILTER(B3:C,B3:B<>"")),"Select Col2"))},2,FALSE))})

This formula will produce the header (which can be changed within the formula itself as desired) and all results.

For those who want to know how this formula works, take note of the fact that there is repeating use of the same QUERY:

QUERY(UNIQUE(FILTER(B3:C,B3:B<>"")),"Select Col___")

Without the "Select" clause, this is really just a UNIQUE listing of the values in Col B and Col C. But COUNTIFS can use one column or the other (i.e., Col1 or Col2) of this via the QUERY for its comparisons and counts.

Once that COUNTIFS on the QUERY is done virtually, VLOOKUP can just lookup each concatenated ID&Date to find the count for that row.

Beyond this overview, of course, I encourage anyone who's interested in studying the formula further to take it apart and see what each piece does, separately and then together.