Forum Discussion

Karl_Shen's avatar
Karl_Shen
Copper Contributor
Jul 17, 2024

COUNTIF not working with arguments defined by LET function

I have a data table with key index as XXX_ID (Data!$A:$A), including a list of XXX IDs for each data rows, and there are repetitive IDs in the column as data in other columns are different.

I need the median for the list of how many times each unique XXX ID shows up in XXX_ID column, so I wrote =LET(filter,FILTER(Data!$A:$A,filter criteria),unique,UNIQUE(filter),count_times,COUNTIF(filter,unique),MEDIAN(count_times))
However, the COUNTIF(filter,unique) part returns error!

 

I tested filter and unique parts in separate columns, and use COUNTIF in the third column as COUNTIF(filter result data list, unique result data list), I get a correct result data list showing how many times each unique XXX ID shows up in the filter result data list.

 

I changed COUNTIF(filter,unique) to DROP(FREQUENCY(filter,unique),-1)

This time it works! Not sure if it is a bug with COUNTIF.

  • Countifs() argument criteria_range doesn't work on calculated values. It only works on pure range.

Resources