Forum Discussion
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.
- Harun24HRBronze ContributorCountifs() argument criteria_range doesn't work on calculated values. It only works on pure range.
- Karl_ShenCopper ContributorThanks for the explanation! May I ask why COUNTIF/COUNTIFS don't work with calculated values? If there are no specific reasons behind such setting, how can I raise a ticket to MS Excel development team to review upgrading the two functions?
BTW, I don't see anything regarding not being able to use calculated values in:
https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34
and
https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842