Count unique values once with multiple criteria across multiple sheets

**bteo** · 07-11-2012, 11:25 PM

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.

**MarvinP** · 07-12-2012, 12:08 AM

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.
Read http://www.contextures.com/xlPivot08.html

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.

**bteo** · 07-12-2012, 12:41 AM

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.

**Fotis1991** · 07-12-2012, 02:19 AM

Hi

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

**bteo** · 07-12-2012, 04:32 AM

Thanks Fotis1991,

Here's the sample workbook.

**bteo** · 07-12-2012, 04:45 AM

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

Cheers.

**Fotis1991** · 07-12-2012, 05:47 AM

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?

**bteo** · 07-13-2012, 05:47 AM

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

**Fotis1991** · 07-13-2012, 06:00 AM

You are welcome

Thanks for feed back and also for reb*

Count unique values once with multiple criteria across multiple sheets

LinkBack

Thread Tools

Rate This Thread

Display

Count unique values once with multiple criteria across multiple sheets

Re: Count unique values once with multiple criteria across multiple sheets

Re: Count unique values once with multiple criteria across multiple sheets

Re: Count unique values once with multiple criteria across multiple sheets

Re: Count unique values once with multiple criteria across multiple sheets

Re: Count unique values once with multiple criteria across multiple sheets

Re: Count unique values once with multiple criteria across multiple sheets

Re: Count unique values once with multiple criteria across multiple sheets

Re: Count unique values once with multiple criteria across multiple sheets

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions