Forum Discussion

Dennis Depoorter's avatar
Dennis Depoorter
Brass Contributor
Jan 31, 2020
Solved

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:...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 05, 2020

    Dennis Depoorter 

    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]))
 )
))
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Feb 04, 2020

Dennis Depoorter 

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?

Dennis Depoorter's avatar
Dennis Depoorter
Brass Contributor
Feb 04, 2020

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

 

Resources