Forum Discussion
Using wildcards within a IF statement
All,
I have the following formula:
=IF(J53<>"";(SUM((IF(FREQUENCY(IF(('10 Accounts with tasks'!$M$2:$M$50000=K$51)*('10 Accounts with tasks'!$E$2:$E$50000>=$B$1)*('10 Accounts with tasks'!$E$2:$E$50000<=$D$1)*('10 Accounts with tasks'!$H$2:$H$50000="Dermatologist")*('10 Accounts with tasks'!$A$2:$A$50000=$A53);MATCH('10 Accounts with tasks'!$B$2:$B$50000;'10 Accounts with tasks'!$B$2:$B$50000;0));ROW('10 Accounts with tasks'!$B$2:$B$50000)-ROW('10 Accounts with tasks'!$B$2)+1);1))));"")This formula counts the distinct amount of client visits from a list of all visits
Example:
- 1 client has 3 visits ==> result for this client will be 1
- 1 client has 1 visit ==> result for this client will be 1
- 1 client has 0 visits ==> result for this client will be 0
- 1 client has 8 visits ==> result for this client will be 1
FOR THE EXAMPLE ABOVE THE RESULT AS SHOWN IN THE CELL IS: 3
This formula works perfect 😄
BUT: now I need to use wildcards into the if statement --> that doesn't work 😞
So i tought: let me place a countif in the if statement:
=IF($J43<>"";(SUM((IF(FREQUENCY(IF(COUNTIFS('10 Accounts with tasks'!$H:$H;"<>"&"Dermatologist";'10 Accounts with tasks'!$A:$A;$C43&"*";'10 Accounts with tasks'!$E:$E;"<="&$D$1;'10 Accounts with tasks'!$E:$E;">="&$B$1; '10 Accounts with tasks'!$M:$M;D$41)>0;MATCH('10 Accounts with tasks'!$B$2:$B$50000;'10 Accounts with tasks'!$B$2:$B$50000;0));ROW('10 Accounts with tasks'!$B$2:$B$50000)-ROW('10 Accounts with tasks'!$B$2)+1);1))));"")
But then I get the value not available error 😞
Someone know what's wrong?
Many thanks in advance,
Dennis
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])) ) ))
20 Replies
In general IF doesn't work in comparing text with wildcards, equivalent could be ISNUMBER(SEARCH(<TextPart>,<range>))
If you are on Excel with dynamic arrays, formula to calculate number of unique clients with criteria could be
=COUNTA(UNIQUE( FILTER('10 Accounts with tasks'!$B$2:$B$50000, ISNUMBER(SEARCH($A53,'10 Accounts with tasks'!$A$2:$A$50000))* ('10 Accounts with tasks'!$H$2:$H$50000="Dermatologist")* ('10 Accounts with tasks'!$E2:$E$50000>=$B$1)* ('10 Accounts with tasks'!$E$2:$E$50000<=$D$1)* ('10 Accounts with tasks'!$M$2:$M$50000=K$51) ) ))or like.
Hi, SergeiBaklan
Many thanks for your response... the result I get is 1, what isn't correct 🙂
I uploaded the file with your formula.
See my previous post for my explanation on what i want!
In you variant of formula latest criteria (Table3[Account Name]=J$14&"*") also shall be changed on ISNUMBER(SEARCH(..
Entire formula
=COUNTA(UNIQUE( FILTER(Table3[Account ID], 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])) ) ))returns 5. I used here structured references working with Table.
Based on the answers on my question, I think there is no solution for my problem?
So my question short:
I need to use wildcards within the if statement in a sum frequency / match formula like the formula above. I'm aware of the fact that wildcards are not possible within a if-statement, but when you replace the if with a countif and check if the value is >0, it should be possible...
Best,
Dennis
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?
Riny_van_EekelenTHANKS for you reply
So... as you requested:
I uploaded an example file (copy of the real file with example data).
The dashboard tab had 4 tables:
Upper left
- All visits to pharmacists (if 1 pharmacist is visited twice, it counts as 2)
Upper right
- All visits to unique pharmacists (if 1 pharmacists is visited twice, it counts as 1)
Down left
- All visits to 3 key accounts (all visits to Mega Pharma, MediumPharma and Klein pharma)
Down right
- All visits to unique key accounts (if 1 key account is visited twice, it counts as 1)
The upper right table has a problem, but I resolved it this way with a work around:
('10 Accounts with tasks'!$A$2:$A$30=$A6)I refer to a cell that I'll make "invisible" with the full name instead of only the first name
The best option would be:
('10 Accounts with tasks'!$A$2:$A$30=$I6&"*")what won't work because of the wild card in a IF statement
The problem is the same in the down right table, but I can't solve it there:
What do I want to see in cell J16:
- All visits at a MegaPharma store between 01/01/2020 and 31/01/2020 by Maxime
Because MegaPharma has a lot of stores with names like MegaPharma 001, MegaPharma 002,... I need to use wildcards in my IF statement (what won't work).
So my question: how can I make it work?
The result for Maxime between 01/01/2020 and 31/01/2020 should be:
MegaPharma: 9
Medium Pharma: 4
Klein pharma: 2
THANKS!!!
