Excel--Let & Lambda, dynamic array count of instances

The following formula produces a list of results in a single cell as expected.

=BYROW(FILTER(M4#,{1,0}),
    LAMBDA(a,
        LET(
                 varARR, SORT(UNIQUE(FILTER(FILTER(rngGapAwarded,((rngAwardedLCAT=a)*((rngEmployeeNameAwarded="TBD")+(rngEmployeeNameAwarded="")))),{0,0,0,1,1,0,0,1,0,1,1}),FALSE),4),

                 TEXTJOIN(CHAR(10),,
                     INDEX(varARR,,1)&"  "&
                     INDEX(varARR,,2)&"  "&
                     INDEX(varARR,,3)&"  POP: "&
                     TEXT(INDEX(varARR,,4),"m/d/yy")&" -- "&
                     TEXT(INDEX(varARR,,5),"m/d/yy")
                 )
           )
     )
)

What I desire is to add a COUNT to each "row" of the result; if the UNIQUE result consisted of 5 "records" for the first row, and 2 "records" for the second row, the displayed value would be:

The results of the above formula, currently are

SomeLCAT PROJECT CODE LOCATION POP m/d/yy -- m/d/y
PROJECT2 CODE2 LOCATION2 POP m/d/yy -- m/d/y

In another column I have a formula which provides the TOTAL COUNT using

=BYROW($H4#,LAMBDA(a, IFERROR(ROWS(FILTER(rngGapAwarded,((rngAwardedLCAT=a)*((rngEmployeeNameAwarded="TBD")+(rngEmployeeNameAwarded=""))))),0)))

But I cannot figure out how to add a count of instances to my formula above so that I get the details.

Randy

@JvdV:

Sample Data:

Data Table:

Results Table:

EDIT FOLLOWING @JvdV Solution:

The formula provided by @JvdV works if there are no empty rows in the range.

=LET(a,A2:K25,b,SORT(FILTER(a,(INDEX(a,,2)="TBD")+(INDEX(a,,2)="")),10),c,UNIQUE(TAKE(b,,1)),IF(ISERROR(TAKE(c,1)),"",REDUCE({"Labor Category","Total Gaps","Projects with Gaps"},c,LAMBDA(d,e,LET(f,FILTER(b,TAKE(b,,1)=e),VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g)))," Required for",TEXT(CHOOSECOLS(g,4,5,8,10,11),{"","","@ POP:","mm/dd/yyyy","-- mm/dd/yyyy"}))))))))))))

As noted in my comment, the raw data worksheet utilizes NULL/BLANK rows to separate projects and I do not have the ability to change this as this is a shared workbook. While effort has been made to enforce data validation, my co-workers will find creative ways to break the results. So, I have to find a solution that accounts for this.

After removing the blank rows in the sample data, I did some further "testing" with the following results:

1. If column(A) is null/blank the formula returns #VALUE at M2. This maybe able to be solved "on-the-fly" by returning "TBD" for blank cells, without changing the source data?
2. If column(B) is null/blank and the result in column(O) would have multiple values, then the formula returns returns #VALUE at (O#). When (O#) result is a single entry there is no error. Again, this maybe able to be solved "on-the-fly" by returning "TBD" for blank cells, without changing the source data?
3. If column(J) or (K) is null/blank or NOT a date and the result in column(O) would have multiple values, then the formula returns #VALUE at (O#). With the same conditions, if (O#) result is a single entry, the result displays with the null/blank as a 0 or text (i.e. TBD). In this error case, TBD works for single entry, but fails for multi-entries, so does not seem like an "easy" fix.

I believe solving 1, 2, and 3 will negate the issue with the empty rows in the dataset. Looking in detail at the raw data in the actual workbook, there are many instances of these "issues" but in the context of the data make sense why they exist, I must find a solution that accounts for these and still works.

Additional troubleshooting:
Using this data set (note empty row and cells), the results are as expected using the formula shown. This is true on the actual data set unless the conditions of Issue 2 and 3 (above) exist:

=LET(a,rngGapAwarded,b,SORT(FILTER(a,((INDEX(a,,1)<>"")*((INDEX(a,,2)="TBD")+(INDEX(a,,2)="")))),1),c,UNIQUE(TAKE(b,,1)),IF(ISERROR(TAKE(c,1)),"",REDUCE({"Labor Category","Total Gaps","Projects with Gaps"},c,LAMBDA(d,e,LET(f,SORT(FILTER(b,TAKE(b,,1)=e),10),VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g)))," Required for",TEXT(CHOOSECOLS(g,4,5,8,10,11),{"","","@ POP:","mm/dd/yyyy","-- mm/dd/yyyy"}))))))))))))

Using this sample data set, if B20 is "" a #VALUE ERROR is received in O5 only; the rest of the results table works. Further, when there are multiple results to be reported in O, if a Column D, E, H, J, or K cell is empty or mismatched a #VALUE error is received in O. I tested this by randomly deleting/changing values. I think my problem is in the MMULTI function, but I am unfamiliar with this function to do further troubleshooting....

UPDATE:
I have updated the formula provided to:

=LET(a,rngGapAwarded,b,SORT(FILTER(a,((INDEX(a,,1)<>"")*((INDEX(a,,2)="TBD")+(INDEX(a,,2)="")))),1),c,UNIQUE(TAKE(b,,1)),IF(ISERROR(TAKE(c,1)),"",REDUCE({"Labor Category","Total Gaps","Projects with Gaps"},c,LAMBDA(d,e,LET(f,SORT(FILTER(b,TAKE(b,,1)=e),10),VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g)))," Required for",TEXT(CHOOSECOLS(g,4,10,11),{"@ POP:","mm/dd/yyyy","-- mm/dd/yyyy"}))))))))))))

Which removed errors that where being caused by non-matching data. Which has lead me to the current issue:

There is something limiting the data from this portion of the fx VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g))) to 4 rows. Does not matter how many instances get counted...if the output is greater than 4 rows a #VALUE error is received. Which can be tested with this dataset:

In this state there is an error but if J6 is changed to the same as J5, thereby limiting the row output to 4, same number of instances though, there is no error. What am I missing in the formula from @JdvJ that is setting a maximum output?

Edited out this text as not applicable.
> Is this a matter of the MMULT fx? After reading up on this function,
> the "columns" being joined by the text join is 4. MS states for the
> MMULT fx:
>
> > The number of columns in array1 must be the same as the number of rows
> > in array2, and both arrays must contain only numbers.
>
> > MMULT returns the #VALUE! error when:
> > -Any cells are empty or contain text.
> > -The number of columns in array1 is different from the number of rows in array2.
>
> This would make sense why a #VALUE error is received if the rows of
> the textjoin exceed 4. How to overcome this???

I spent the weekend testing on my actual dataset and on sample data. When I remove this section of the formula,

," Required for",TEXT(CHOOSECOLS(g,4,10,11),{"@ POP:","mm/dd/yyyy","-- mm/dd/yyyy"})

I can get every expected line with the correct total of instances, but I obviously lose the information. With this in mind, I added back in a portion of the function:

," Required for",TEXT(CHOOSECOLS(g,4),{"@ POP:"})

Which again created errors in some rows. So what would cause this?

I couldn't really figure out what column to sort on, nor did I know which column belongs to which named range, but tried the below to the best of my abilities trying to find out what it is you want to achieve:

Formula in M2:

=LET(a,A2:K25,b,SORT(FILTER(a,(INDEX(a,,2)="TBD")+(INDEX(a,,8)="")),10),c,UNIQUE(TAKE(b,,1)),IF(ISERROR(TAKE(c,1)),"",REDUCE({"Labor Category","Total Gaps","Projects with Gaps"},c,LAMBDA(d,e,LET(f,FILTER(b,TAKE(b,,1)=e),VSTACK(d,HSTACK(e,ROWS(f),TEXTJOIN(CHAR(10),,BYROW(UNIQUE(f),LAMBDA(g,TEXTJOIN(" ",,SUM(N(MMULT(N(f=g),SEQUENCE(COLUMNS(f),,,0))=COLUMNS(g)))," Required for",TEXT(CHOOSECOLS(g,4,5,8,10,11),{"","","@:","mm/dd/yyyy","-- mm/dd/yyyy"}))))))))))))