Excel #VALUE error with COUNTIF inside LET function when using FILTER

Question

I am trying to write a formula using LET, FILTER, COUNTIF and dynamic ranges to count occurrences of unique values in a table column.

I have an excel table tbl with one column df_col like this:

df_col
a
a
b
c
c
c

I wrote the following excel function that outputs the unique values alongside the count in the table.

=LET(
  col_all; tbl[df_col];
  col_uniq; UNIQUE(col_all);
  col_count; COUNTIF(col_all; col_uniq);
  HSTACK(col_uniq; col_count)
)

The result I get is what I have expected

a	2
b	1
c	3

Next I wanted to add some FILTER on the column to do this for a subset of the data.

=LET(
  col_filtered; FILTER(tbl[df_col]; tbl[df_col]<="b");
  col_uniq; UNIQUE(col_filtered);
  col_count; COUNTIF(col_filtered; col_uniq);
  HSTACK(col_uniq; col_count);
)

The result throw errors. Seems the COUNTIF function is not working on the retrieved list.

a	#VALUE
b	#VALUE
#N/A	#VALUE

Running the formula evaluation shows an error in the COUNTIF part. Nevertheless doing these step-by-step in excel without the LET function works fine.

What's the issue?

Answer 1

COUNTIF function requires a range of cells.

In your first example the reference to the table is converted as a range of cells.

In the second one, and in the comments the passed parameters are arrays, which is not accepted by the function. This results in a #VALUE error

Info: Microsoft

Answer 2

I found a workaround for the COUNTIF reference restriction that works for my case

=LET(
  col_all; FILTER(tbl[df_col]; tbl[df_col]<="b");
  col_uniq; UNIQUE(col_all);
  col_count; BYROW(col_uniq; LAMBDA(x; SUM(--(col_all=x))));
  res;HSTACK(col_uniq; col_count);
  res
)