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

Dennis Depoorter 

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.

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

      Dennis Depoorter 

      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.

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

        SergeiBaklanwhy is there is difference of 4 with the actual result it should be 9 (see JPEG attached)

         

        I filtered on

        • "Assigned To": = Maxime "*"
        • "Day visited": = January 2020
        • "Account record type": <> Dermatologist
        • Account name contains "MegaPharma"

        If you count the account names with MegaPharma you can count 10 of them with a double on "MegaPharma 011" so the result should be 9

         

         

Resources