Search code examples
excelexcel-formula

Alternatives to COUNTA

I have an excel formula that goes like this:

=COUNTA(UNIQUE(FILTER(Sheet2!C:C,(Sheet2'!Y:Y="Yes")*(Sheet2!J:J>=Sheet1!C2)))

The C column in the second sheet is the clients names. The Y column is the question if it's a new client. The J column is the date of the receipt and C2 in the first sheet is the today date.

However, it always show me the answer 1, even if it's truly 0 because there's no clients with receipt in the future yet. Is there a formula that can do the same thing, which is to give me the number of new clients (only counted once) that have receipt in the future ?

Solution

  • Here is one way of doing this using SUM() function, you can also use ROWS() function, but it would return an error if there is none.

    enter image description here

    =SUM(--(UNIQUE(FILTER(B2:B11,(C2:C11="Yes")*(D2:D11>=F2),""))<>""))

    Alternatively, one can use ROWS() as mentioned:

    =ROWS(UNIQUE(FILTER(B2:B11,(C2:C11="Yes")*(D2:D11>=F2),"")))

    Sample data posted based on the context of the OP.

    One more great alternative as suggested by Tom Sharpe Sir

    =SUM(--(COUNTIFS(A:A,UNIQUE(A:A),B:B,"Yes",C:C,">="&E2)>0))