Search code examples
excelcount-unique

Add Partial Text Match as Criteria to a Count Unique Formula

I've been trying to add a partial text match as a criteria to the formula below, but with no success so far:

=SUM(--(FREQUENCY(IF(Sales[ClientID]=A21;IF(Sales[Operation]="Sale*";Sale[InvoiceNumber]));Sale[InvoiceNumber])>0))

The piece IF(Sales[Operation]="Sale*"; is the one that when added, always give me 0 as the result.

Here's some data:

Sales Table
Date         ClientID     Operation       InvoiceNumber     Total
01/01/2019   18090        Sale Adv        101010101         100
01/02/2019   20897        Sale Cash       105327892         100
01/03/2019   18090        Sample          41357398         100
01/01/2019   30456        Sale Check      43167429         100
10/04/2019   779584       Sale Cash       4326719         100
01/05/2019   30456        Refused         34162781         100
01/01/2019   90909        Sale Cash       3412679821         100

Results Table
ClientID    Purchase Frequency
779584        ???
Solution

  • Here's the solution I borrowed from Scot Craner, in case someone else falls into the same question:

    =SUM(--(FREQUENCY(IF(Sales[ClientID]=A21;IF(ISNUMBER(SEARCH("Sale";Sales[Operation]));Sale[InvoiceNumber]));Sale[InvoiceNumber])>0))

    Thanks everyone!