Excel count Unique ID's between two dates a distinct count of another column for each ID-CodePudding

I am struggling with an Excel formula. I am trying to count the number of Unique ID's between two dates (I have that formula working), but I also want to count the SignUpRoles for each unique ID that qualified between the two dates. I am using O365.

Here is how I am capturing the UserId counts in K10:14

=SUM(IF(FREQUENCY(IF(Weartime13[RecordDate]>=K5,IF(Weartime13[RecordDate]<=J5,MATCH(Weartime13[UserId],Weartime13[UserId],0))),ROW(Weartime13[UserId])-ROW(A5) 1),1))

Here is the worksheet I am working with:

EDIT

As noted by @JosWoolley, a structured reference would have been preferable to row(A5). I would suggest:

=SUM(IF(FREQUENCY(IF(Weartime13[RecordDate]>=O5,IF(Weartime13[RecordDate]<=J5,IF(Weartime13[SignUpRole]=L9,MATCH(Weartime13[UserId],Weartime13[UserId],0)))),ROW(Weartime13[UserId])-ROW(Weartime13[#Headers])),1))

But what if you want to pull these formulas across so that L9 changes automatically to M9 and N9 but it still references the same table columns? I had to look this one up and the answer is:

=SUM(IF(FREQUENCY(IF(Weartime13[[RecordDate]:[RecordDate]]>=$O5,IF(Weartime13[[RecordDate]:[RecordDate]]<=$J5,IF(Weartime13[[SignUpRole]:[SignUpRole]]=L9,MATCH(Weartime13[[UserId]:[UserId]],Weartime13[[UserId]:[UserId]],0)))),ROW(Weartime13[[UserId]:[UserId]])-ROW(Weartime13[#Headers])),1))

Formula for the last row using count & filter would be

=COUNT(UNIQUE(FILTER(Weartime13[UserId],(Weartime13[RecordDate]<=J5)*(Weartime13[RecordDate]>=O5))))

for the total and

=COUNT(UNIQUE(FILTER(Weartime13[[UserId]:[UserId]],(Weartime13[[RecordDate]:[RecordDate]]<=$J5)*(Weartime13[[RecordDate]:[RecordDate]]>=$O5)*(Weartime13[[SignUpRole]:[SignUpRole]]=L9))))

pulled across for the SignUpRole breakdown, assuming UserId is numeric.

But what if you wanted a single formula that could be pulled both down and across for the whole range of dates and roles? This could be arranged as follows:

=COUNT(UNIQUE(FILTER(Weartime13[UserId],(Weartime13[RecordDate]<=J$5)*(Weartime13[RecordDate]>=INDEX($K$5:$O$5,ROW()-ROW($9:$9))))))

for the total and

=COUNT(UNIQUE(FILTER(Weartime13[[UserId]:[UserId]],(Weartime13[[RecordDate]:[RecordDate]]<=$J$5)*(Weartime13[[RecordDate]:[RecordDate]]>=INDEX($K$5:$O$5,ROW()-ROW($9:$9)))*(Weartime13[[SignUpRole]:[SignUpRole]]=L$9))))

for the role columns.

Is there a simpler way of doing this whole thing? Maybe with pivot tables or perhaps power query, but that would be a separate answer :-)