Forum Discussion
Using wildcards within a IF statement
If unique Account Name are counting, when apply FILTER to this column and wrap by UNIQUE
=COUNTA(UNIQUE( FILTER(Table3[Account Name], ISNUMBER(SEARCH($I16&"*",Table3[Assigned To: Full Name]))* (Table3[Account Record Type]<>"Dermatologist")* (Table3[Day Visited]>=$B$1)* (Table3[Day Visited]<=$D$1)* ISNUMBER(SEARCH(J$14&"*",Table3[Account Name])) ) ))
Not sure that the lack of answers means that there is no solution to your problem. Though, it's difficult to visualise your problem without a sample of your workbook (of course, without confidential information) to see the formula in action. Could you upload such an example?
Thanks for your reply... i tried to make a workbook with some test data to explain myself a little better.
At the dashboard page you see 3 small overview tables:
first table (upper left):
- I count all the visits a sales rep had been made
second table (upper right)
- I count all the visits to a unique pharmacist
- example: if 1 pharamcists has 2 visits in the same period, I count it as one because I want to know in the end how many pharmacists has been visited
third table (down left)
- I count all the visits to our key account
fourth table (down right)
- I want to count all the visits to a unique key account
- expl: in the end I want to know how many unique locations of a key account are visited (so locations should not be count twice!!! --> same as the upper right table, but with key accounts
For the first 3 tables, there isn't a big problem. Only with the upper right there is a small problem I can fix with a hide cell. --> i don't use ('10 Accounts with tasks'!$A$2:$A$100=$I6&"*") because that don't work, but I refer to a hide cell ('10 Accounts with tasks'!$A$2:$A$100=$A6);
The problem is in the last table. Because a key account has multiple locations (MegaPharma 001,MegaPharma 002, ...) and I can't use wildcards, I'm a little stuck on my formula.
Normally, I use countif in such a case, but that don't work here for some reason.
So my question is, how can I make this work?
In my example the result should be:
- the unique visits of MegaPharma's between 01/01/2020 and 31/01/2020 by Maxime are: 9 (total visits are 10)
- the unique visits of Medium Pharma's between 01/01/2020 and 31/01/2020 by Maxime are: 4 (total visits are 5)
- the unique visits of Klein Pharma's between 01/01/2020 and 31/01/2020 by Maxime are: 2 (total visits are 5)
Thanks!!!
Patrick2788That is what I need, but not in a Pivot 🙂🙂
PowerPivot is capable of arriving at a Distinct Count. It's simpler than using a complex array formula.
