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't meet. It returns 1 value instead of 0.

=COUNTA(UNIQUE(FILTER(B:B,(A:A=H3)*(C:C=J1))))

Forexample in following picture, for programme adbfeg there is no part number on supply route 2159A, it is still returning value as 1 instead of 0 in column L.


Could you please help me in finding out what's the error? Also, can you please guide how can I calculate using same formula for blank supply routes in column M. I'm not sure how to attach the file in this post as I'm new to community. If someone can please guide me, I'll attach the file as well.


 

excel

1 Reply

Sort By
  • 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