Please provide example workbook so we can see the data layout

File attached

KR

Hi Pedro,

The file is not attached. When you click on Attachments, you can select your file via Add Files and then finalise the procedure by clicking on Start Upload.

BR,

Lionel

Now with the file attached,

Thanks for the help

Hi Pedro

I don't know which columns you are referring to but I guess it is columns A, C (maybe F) and L.

I would suggest using a Pivot Table as it is quick and easy to use.

Refer attached.

If it is not what you wanted, then let us have a sample of your expected results.

Sunny

(Excel 2019)

Many thanks for the reply,

My problem is that the pivot table counts the total customers with 1 reference or both and the same customer will be repeated if you buy the 2 refs. And what I need to know is the maximum customer reach for the product (be it normal or promo)

For example,

Customer A - bought the 2 references (normal + promo)

Customer B - bought 1 reference (normal)

Customer A will be counted 2 times in the pivot table

Sorry if I don't make myself understood!

Hi, Pedro,

I'm not sure I've grasped all the finer points of the reasoning. I suggest this solution based on a pivot table. In sheet_2, you have three results:

• A3: the number of distinct orders by Delegado and by Farmacia. You will therefore have one result even if there were several orders.
• E3: the number of orders per Delegado. 
• H3: the number of orders by Farmacia.

BR,

Lionel

Thanks again for your attention

To know the number of customers with product X (since product x has another that is an "xx" promo, but for me, it is indifferent, as it counts as 1 product), it would have to be with formula because with the pivot table you don't do that interpretation and you will always count individually, correct?

Column P has a manual calculation, counting the customer with "1" and repeating with "0", I can identify 482 customers with the product x and/or its promo or both. I don't know if I explain myself correctly and if what I intend makes sense to you.

As I have to reproduce sales reports on a regular basis, this type of analysis is important to know the numerical distribution

Hi Pedro

It looks like you wanted to count the unique address (column H) since you considered them to be different customers (even if they are the same pharmacy).

You can try this formula

=SUMPRODUCT(1/COUNTIF(H4:H787,H4:H787))

It will give you 475.

If you want to have values like in column P, then you will have to sort your data by column C and then H.

You can then use a formula like =IF(H4=H3,0,1) in column Q and then copy downwards. The total will also be 475.

Hope this is what you wanted.

Sunny

Thanks very much for help Sunny