Forum Discussion

SehrishFaiz8's avatar
SehrishFaiz8
Occasional Reader
Mar 06, 2025

CountA, Unique & Filter Combined Formula Query

I'm using following formula to count unique values if criteria in 2 different columns meet. It is working fine and counts unique value when both critieria meet. However, if one column criteria doesn'...
excel
joelb95's avatar
joelb95
Brass Contributor
Mar 06, 2025

This might be a little wordy for you, but try this formula instead:

=LET(
    count_range,A1:A5,
    criteria1,(A1:A5)="Check",
    criteria2,(A1:A5)="Check2",
    filtered_results,
        FILTER(
            A1:A5,
            criteria1*criteria2
    ),
    result,
    IF(
        ISERROR(filtered_results),
        0,
        COUNTA(UNIQUE(filtered_results))
    ),
    result
)



The basic idea is that COUNTA effectively acts as a cell counter regardless of the contents of that cell.  Given that a filter function returns a "#CALC!" error when there are no matching results (you did not choose a default value in your filter function), this means that when you do not match both of your criteria, the FILTER will return the calc error, then UNIQUE will return the calc error, and then COUNTA will count the cell containing the error value as 1.


If you don't want an error value to count with COUNTA, I think you are out of luck.  Something like =SUM(IF(NOT(ISERROR(A1:A5)),1)) would work.

Resources