Excel LET Formula Results in #CALC! Error When Applying a Filter

Question

I am trying to create a formula that shows a list of Top 20 staff names based on a grouped count. Futhermore, I am trying to dynamically apply a filter to said list.

The data is being taken from the following table NoCstNoAcr, that consists of a list of users, their respective company and branch.

I can successfully show the top 20 usernames based on the file count - but when I try to apply a branch filter (i.e., show the top 20 usernames in a specific branch grouped by file count), the formula returns #CALC! due to nested arrays.

Below is my code:

=LET(
    branchFilter; D33;
    NoCstNoAcrHeaders; NoCstNoAcr[#Headers];
    branchIndex; MATCH("Branch Code"; NoCstNoAcrHeaders; 0);
    operatorIndex; MATCH("Operator Full Name"; NoCstNoAcrHeaders; 0);
    filteredData; IF(ISBLANK(branchFilter);
                     NoCstNoAcr;
                     FILTER(NoCstNoAcr; INDEX(NoCstNoAcr[Branch Code];;)=branchFilter));
    operatorNames; UNIQUE(INDEX(filteredData;;operatorIndex));
    fileCounts; MAP(operatorNames; LAMBDA(name; COUNTIF(INDEX(filteredData;;operatorIndex); name)));
    sortedData; SORTBY(HSTACK(operatorNames; fileCounts); fileCounts; -1);
    TAKE(sortedData; 20)
)

The branch filter is residing in cell D33.

Can someone help me understand why this error occurs and how to fix the formula so it works correctly when a branch filter is applied?

Answer 1

WIth COUNTIFS instead

I think the error is due to COUNTIF's first parameter, as it cannot be an array (only range).

I wonder whether you could try this to get the fileCounts:

1. Using COUNTIFS get counts matching names and branch.
2. Combine with Names.
3. Get unique rows with name, count.
4. Filter out rows with zero count.

(Please change , to ;)

=LET(
    branchFilter, D33,
    fileCounts_step1, UNIQUE(
        HSTACK(
            NoCstNoAcr[Operator Full Name],
            COUNTIFS(
                NoCstNoAcr[Operator Full Name], NoCstNoAcr[Operator Full Name],
                NoCstNoAcr[Branch Code], IF(
                        LEN(TRIM(branchFilter)) > 0,
                        branchFilter,
                        NoCstNoAcr[Branch Code]
                    )
            )
        )
    ),
    fileCounts, FILTER(fileCounts_step1, INDEX(fileCounts_step1, , 2) > 0),
    sortedData, SORT(fileCounts, 2, -1),
    result, TAKE(sortedData, 20),
    IFERROR(result, "No results")
)

Or

=LAMBDA(branchFilter, top,
    LET(
        fileCounts_step1, UNIQUE(
            HSTACK(
                NoCstNoAcr[Operator Full Name],
                COUNTIFS(
                    NoCstNoAcr[Operator Full Name], NoCstNoAcr[Operator Full Name],
                    NoCstNoAcr[Branch Code], IF(
                        LEN(TRIM(branchFilter)) > 0,
                        branchFilter,
                        NoCstNoAcr[Branch Code]
                    )
                )
            )
        ),
        fileCounts, FILTER(fileCounts_step1, INDEX(fileCounts_step1, , 2) > 0),
        sortedData, SORT(fileCounts, 2, -1),
        result, TAKE(sortedData, 20),
        IFERROR(result, "No results")
    )
)(D33, 20)