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]))
 )
))
Dennis Depoorter's avatar
Dennis Depoorter
Brass Contributor
Feb 05, 2020

Hi Sergei,

 

Thanks for your response!

I removed the "unique", the result is indeed 10 but it has to be 9:

MegaPharma 011 is twice in the list, and should be count as 1

Al the other MegaPharma's are just once in the list, so the count of them is then 9.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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]))
 )
))
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 05, 2020

    Dennis Depoorter 

    Yes, that could be better - you know your business logic

  • Dennis Depoorter's avatar
    Dennis Depoorter
    Brass Contributor
    Feb 05, 2020

    SergeiBaklan 

     

    Thanks for all the info... I found another quick fix in my dashboard. If there are 0 visits in the left table, then there are also 0 visits in the right. So I first check if there are visits or not. If there are no visits, the result is 0, else he starts the array function

     

    =IF($J43="";"";IF(E43=0;0;COUNTA(UNIQUE(FILTER(AccountWithTasks[Account Name];ISNUMBER(SEARCH($J43;AccountWithTasks[Assigned To: Full Name]))*(AccountWithTasks[Day Visited]>=$B$1)* (AccountWithTasks[Day Visited]<=$D$1)*(AccountWithTasks[Status(1)]=L$41)*(AccountWithTasks[Account Record Type]<>"Dermatologist"))))))

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 05, 2020

    Dennis Depoorter 

    That is since array function can't return empty array and gives #CALC! error. COUNTA calculates non-blank elements, thus calculates one with error as well and returns 1. Quick fix could be

    =SUMPRODUCT(--(IFERROR(
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]))
 )),"#==#")<>"#==#"))
  • Dennis Depoorter's avatar
    Dennis Depoorter
    Brass Contributor
    Feb 05, 2020

    SergeiBaklan 

     

    I rebuild the formula myself as an "exercise" and read about the COUNTA UNIQUE FILTER functions, and understand the formula now. The only thing is... there is a small "bug" in the formula I don't get solved: If the result of the formula should be 0 or 1, the result shown is always 1. So 0 isn't "possible" as a result what is a little bit strange...

  • Dennis Depoorter's avatar
    Dennis Depoorter
    Brass Contributor
    Feb 05, 2020

    SergeiBaklanThanks!!!!

     

    Only one more strange thing is that he also gives a "1" visit for Dennis --> Klein Pharma while there are no visits at Klein Pharma by Dennis.

     

     

     

Resources