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

     

     

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

      Dennis Depoorter 

      My guess was you'd like to calculate number of unique accounts. If repeating accounts are to be counted, just remove UNIQUE

       

      =COUNTA(
FILTER(Table3[Account ID],
    ISNUMBER(SEARCH($I6&"*",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]))
 )
)

       

      Result will be 10, not 9. I filtered the table accordingly, it shows 10 records

      • 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.

Resources