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

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

sales report example data.xlsx27 KB

Patrick2788

Silver Contributor

Feb 05, 2020

Dennis Depoorter

PowerPivot is capable of arriving at a Distinct Count. It's simpler than using a complex array formula.

Dennis Depoorter

Brass Contributor

Feb 05, 2020

Patrick2788Hi patrick,

True, but pivots are not the solutions here for me...

I always see pivots as a way to represent data quick, but it's not possible to show the data exact how I want it. So it's just a fast way to show data, but not to make dashboards. (my opinion)

Forum Discussion

Using wildcards within a IF statement