Counting within a formula

[BlahQz]

I have a block of data with 2 columns. One for names, and another for dates. In the Names column, there are 5 unique names but have multiple entries (User1 - User5) and beside each is a corresponding date.

What I've done so far is create a formula in cell E19 that will show the unique names in the list that have dates 3 days ago (excluding weekends). For example today is Dec 9th, so 3 days ago (including today's date) is actually Dec 5th. My goal is to have a formula that shows the unique name as well as the number of times it fits the criteria of the formula I made.

Apologies for having to upload a screenshot, the Xl2bb addin didn't work for me maybe due to restrictions on my machine, not sure.

Formula in cell E19:

=TEXTJOIN(", ",TRUE,(SORT(UNIQUE(IF(INT(B2:B16)<=WORKDAY.INTL(TODAY(),-2,"0000011"),A2:A16,"")))))

*The answers part in the screenshot is just for reference

 

[SunnyAlv]

try

=IFERROR(LET(names,A2:B16,dates,B2:B16,crrntday,TODAY()-4,
data,FILTER(names,INT(dates)<crrntday),
TEXTJOIN(", ",,CONCATENATE(UNIQUE(CHOOSECOLS(data,1))," ("&SCAN(0,UNIQUE(CHOOSECOLS(data,1)),LAMBDA(a,b,SUM(--(b=CHOOSECOLS(data,1)))))&")"))),"No Day")

** U Can change 'crrntday' with any day

 

[Peter_SSs]

the Xl2bb addin didn't work for me

Try the suggestions in this thread.

 

[Fluff]

Another option

=LET(g,GROUPBY(A2:A16,B2:B16,COUNT,0,0,,INT(B2:B16)<=WORKDAY(TODAY(),-2)),t,TEXTJOIN({" (","),"},0,FILTER(g,INDEX(g,,2)>0),""),LEFT(t,LEN(t)-1))

 

[BlahQz]

try

=IFERROR(LET(names,A2:B16,dates,B2:B16,crrntday,TODAY()-4,
data,FILTER(names,INT(dates)<crrntday),
TEXTJOIN(", ",,CONCATENATE(UNIQUE(CHOOSECOLS(data,1))," ("&SCAN(0,UNIQUE(CHOOSECOLS(data,1)),LAMBDA(a,b,SUM(--(b=CHOOSECOLS(data,1)))))&")"))),"No Day")

** U Can change 'crrntday' with any day


View attachment 120208

This one worked best for me I really appreciate it SunnyAlv

 

[BlahQz]

Another option

=LET(g,GROUPBY(A2:A16,B2:B16,COUNT,0,0,,INT(B2:B16)<=WORKDAY(TODAY(),-2)),t,TEXTJOIN({" (","),"},0,FILTER(g,INDEX(g,,2)>0),""),LEFT(t,LEN(t)-1))

This one unfortunately didn't work for me, gave a #Name error. It is ok however I got the answer I needed on this one. I appreciate your time on this as well Fluff

 

[Fluff]

Sounds like you don't have GROUPBY yet.

 

[BlahQz]

Sounds like you don't have GROUPBY yet.

No I guess not. I still appreciate the help though. Still amazes me how good you all are at this stuff. Something to aspire to.

 

[BlahQz]

Try the suggestions in this thread.

Thank you Peter I was able to figure out why the addin wasn't working. Had to add where I saved it as a trusted location. I'm set for next time. All the best.

 

[Peter_SSs]

Had to add where I saved it as a trusted location.

That's it. (Or else moved it to an already trusted location. )

I'm set for next time.

Great!