Add Partial Text Match as Criteria to a Count Unique Formula

Question

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        ???

and also demonstrate the expected output based on the sample data. — Terry W, Oct 14 '19 at 21:48
Hi @SolarMike! I've edited the question by adding data to illustrate the problem. Sorry I had it clear in my mind and thought it'd be clear to others as well. — onit, Oct 15 '19 at 01:09
Instead of `Sales[Operation]="Sale*"` use `AND(Sales[Operation]>"Sale",Sales[Operation]<"Salf")` — David García Bodego, Oct 15 '19 at 07:29
Hi @DavidGarcíaBodego, would you mind explaining why "Salf" as a criteria, in this case? Appreciate your attention! — onit, Oct 15 '19 at 11:50
If the wildcard is not working for your and you are trying to find values like `Sale*`, then that ones are the ones between `Sale` and `Salf` on alphabetic order, so the strings on between `Sale` and `Salf`. That group are all the strings `Sale*` — David García Bodego, Oct 15 '19 at 11:59
I ended up using if(isnumber(search( and it worked nicely. Since the criteria is actually in PT; therefore different wordings, I decided to try another approach I had on hands and it worked. Nice to know that it's possible to play around with > followed by words and its alphabet sequence though! Thank you! — onit, Oct 15 '19 at 12:38

score 0 · Accepted Answer · answered Oct 15 '19 at 12:41

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!

Add Partial Text Match as Criteria to a Count Unique Formula

1 Answers1