Excel function LAMBDA COUNTIF

Question

=LET(
Business,B6,
All_nationalities,FILTER(Data[Nationality],Data[Business]=Business),
Unique_nationalities,UNIQUE(All_nationalities),
freq,BYROW(Unique_nationalities,LAMBDA(x,COUNTIF(All_nationalities,x))),
sorted,SORTBY(Unique_nationalities,freq,-1),
INDEX(sorted,1)
)

I want to use formula above to calculate top nationalities for each of the businesses. I get #CALC! error and upon checking it comes from LAMBDA function.

I tried to split each step out to see where the error is. All_nationalities = filtered data from the table Data, for given business Unique_nationalities = filtering out unique nationalities for that business freq = should calculate total number of repeats of each unique nationality for that business

Could anyone help please? Thanks

Try this... =LET( Business, B6, All_nationalities, FILTER(Data[Nationality], Data[Business]=Business), Unique_nationalities, UNIQUE(All_nationalities), freq, MAP(Unique_nationalities, LAMBDA(x, COUNTIF(All_nationalities, x))), sorted, SORTBY(Unique_nationalities, freq, -1), INDEX(sorted, 1) ) — Siddharth Rout
– Siddharth Rout, Commented Oct 13 at 11:25
Hi @SiddharthRout - tried earlier MAP alongside BYROW - both giving the same #CALC! error — neg
– neg, Commented Oct 13 at 11:27
COUNTIF does, I believe, require a range as its first argument, whereas All_nationalities is an array, and not a range. — Spectral Instance
– Spectral Instance, Commented Oct 13 at 11:53
Sample data |----------|-------------| | HU | Lebanese | | HU | Pakistani | | SH | Kuwaiti | | SH | Turkish | | SH | Turkish | | HU | Pakistani | | DU | Chinese | | DU | Chinese | | DU | Chinese | | HU | Pakistani | | SH | Turkish | | SH | Indian | | SH | Indian | | SH | Indian | | SH | Indian | | HU | Sri Lankan | | HU | Sri Lankan | | DU | Taiwanese | | DU | Pakistani | | DU | Pakistani | — neg
– neg, Commented Oct 13 at 13:45
Expected result Top Nationalities by business shortname would be: DU = Chinese; Hu = Pakistani; SH = Indian. But with my formula I can't work out how to do it — neg
– neg, Commented Oct 13 at 13:50

Ike · Accepted Answer · 2025-10-13 14:59:55Z

3

If I understand you correctly (what I am not sure about) then you can use this formula:

=GROUPBY(
    Data[Nationality],
    Data[Nationality],
    COUNTA,
    0,
    0,
    -2,
    Data[Business] = B6
)

edited Oct 13 at 14:59

answered Oct 13 at 14:31

Ike

13.4k6 gold badges20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

neg Oct 13 at 14:50

Thank you very much, Ike. Sorted now. '=INDEX(SORT(GROUPBY(Data[Nationality], Data[Nationality], COUNTA, 0, 0, 1, Data[Business] = B18),2,-1),1)'

Ike Oct 13 at 14:59

The 6th parameter of GROUPBY can be set to -2 (instead of 1): then the result will be sorted descending by the second column ... no extra SORTneeded - I update the answer

Harun24hr · Accepted Answer · 2025-10-14 02:23:35Z

3

Another alternative-

=LET(x,Table1[Nationality],y,HSTACK(x,COUNTIFS(x,x)),SORT(UNIQUE(FILTER(y,Table1[Business Unit]=F1)),2,-1))

answered Oct 14 at 2:23

Harun24hr

37.9k6 gold badges23 silver badges47 bronze badges

Collectives™ on Stack Overflow

Excel function LAMBDA COUNTIF

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related