Using wildcards within a IF statement

Riny_van_EekelenTHANKS for you reply

So... as you requested:

I uploaded an example file (copy of the real file with example data).

The dashboard tab had 4 tables:

Upper left

• All visits to pharmacists (if 1 pharmacist is visited twice, it counts as 2)

Upper right

• All visits to unique pharmacists (if 1 pharmacists is visited twice, it counts as 1)

Down left

• All visits to 3 key accounts (all visits to Mega Pharma, MediumPharma and Klein pharma)

Down right

• All visits to unique key accounts (if 1 key account is visited twice, it counts as 1)

The upper right table has a problem, but I resolved it this way with a work around:

('10 Accounts with tasks'!$A$2:$A$30=$A6)

I refer to a cell that I'll make "invisible" with the full name instead of only the first name

The best option would be:

('10 Accounts with tasks'!$A$2:$A$30=$I6&"*")

what won't work because of the wild card in a IF statement

The problem is the same in the down right table, but I can't solve it there:

What do I want to see in cell J16:

• All visits at a MegaPharma store between 01/01/2020 and 31/01/2020 by Maxime

Because MegaPharma has a lot of stores with names like MegaPharma 001, MegaPharma 002,... I need to use wildcards in my IF statement (what won't work).

So my question: how can I make it work?

The result for Maxime between 01/01/2020 and 31/01/2020 should be:

MegaPharma: 9

Medium Pharma: 4

Klein pharma: 2

THANKS!!!

Thanks for your reply... i tried to make a workbook with some test data to explain myself a little better.

At the dashboard page you see 3 small overview tables:

first table (upper left):

• I count all the visits a sales rep had been made

second table (upper right)

• I count all the visits to a unique pharmacist
• example: if 1 pharamcists has 2 visits in the same period, I count it as one because I want to know in the end how many pharmacists has been visited

third table (down left)

• I count all the visits to our key account

fourth table (down right)

• I want to count all the visits to a unique key account
• expl: in the end I want to know how many unique locations of a key account are visited (so locations should not be count twice!!! --> same as the upper right table, but with key accounts

For the first 3 tables, there isn't a big problem. Only with the upper right there is a small problem I can fix with a hide cell. --> i don't use ('10 Accounts with tasks'!$A$2:$A$100=$I6&"*") because that don't work, but I refer to a hide cell ('10 Accounts with tasks'!$A$2:$A$100=$A6);

The problem is in the last table. Because a key account has multiple locations (MegaPharma 001,MegaPharma 002, ...) and I can't use wildcards, I'm a little stuck on my formula.

Normally, I use countif in such a case, but that don't work here for some reason.

So my question is, how can I make this work?

In my example the result should be:

• the unique visits of MegaPharma's between 01/01/2020 and 31/01/2020 by Maxime are: 9 (total visits are 10)
• the unique visits of Medium Pharma's between 01/01/2020 and 31/01/2020 by Maxime are: 4 (total visits are 5)
• the unique visits of Klein Pharma's between 01/01/2020 and 31/01/2020 by Maxime are: 2 (total visits are 5)

Thanks!!!