# Count unique values once with multiple criteria across multiple sheets

1. ## Count unique values once with multiple criteria across multiple sheets

Hi...
I have a sheet with column A containing invoice date, column B containing customer ID and Column C containing customer types (wholesaler, key account, pharmacy, etc).
My Objective is to count how many customer within a certain category (Pharmacy, wholesaler, etc) that buy from us during a certain span of date.

Let's say...
I want to count how many pharmacy that bought from us during 6th June 2012 to 15th June 2012.
PS: "If Adolf Pharmacy bought from us at 6th June 2012 and 10th June 2012, it will be counted as one."

And i would like the result on another summary sheet.

I have been struggling with this for weeks. Is this possible?

Cheers.

2. ## Re: Count unique values once with multiple criteria across multiple sheets

Hi bteo and welcome to the forum,

Excel has this feature called Pivot Tables from multiple consolidation ranges. I believe this is what you need.

Everything made sense above except for the "counted as one". If you post a workbook with a few sample sheets I can do it for you. It would be best if you read about it first. To post a sample file, click on "Go Advanced" below the message area and then on the paper clip icon above the advanced message area.

3. ## Re: Count unique values once with multiple criteria across multiple sheets

Thx Marvin,

what i mean with "counted as one" is: even if our customer bought from us twice or three times within the span of designated dates - which means the customer ID will show up twice or three times in column B - it will be counted only once.

Actually, i prefer the solution in "functions", so i can treat it as a template for the next report. But I will definitely read the link before i proceed any further.

cheers.

4. ## Re: Count unique values once with multiple criteria across multiple sheets

Hi

I think that we can do using formulas but we need a sample workbook.

5. ## Re: Count unique values once with multiple criteria across multiple sheets

Thanks Fotis1991,

Here's the sample workbook.

6. ## Re: Count unique values once with multiple criteria across multiple sheets

Sorry, forget to mention in the sample workbook... I want the result displayed in sheet2.

Cheers.

7. ## Re: Count unique values once with multiple criteria across multiple sheets

Hi

Here is an option, using a helper column with a COUNTIF function and then using SUMPRODUCT.

So in G2 and copy down.

``Please Login or Register  to view this content.``
Then in Sheet2

``Please Login or Register  to view this content.``
Is this, works for you?

8. ## Re: Count unique values once with multiple criteria across multiple sheets

Works like a charm!!! I'm glad i join this forum. Thanx fotis...

9. ## Re: Count unique values once with multiple criteria across multiple sheets

You are welcome

Thanks for feed back and also for reb*

There are currently 1 users browsing this thread. (0 members and 1 guests)