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?
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