Forum Discussion
Using wildcards within a IF statement
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])) ) ))
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...
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]))
)),"#==#")<>"#==#"))Yes, that could be better - you know your business logic
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"))))))