+ Reply to Thread
Results 1 to 9 of 9

Count unique values once with multiple criteria across multiple sheets

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    Medan, Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,395

    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.
    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    07-11-2012
    Location
    Medan, Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    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.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    07-11-2012
    Location
    Medan, Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    Thanks Fotis1991,

    Here's the sample workbook.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-11-2012
    Location
    Medan, Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    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?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-11-2012
    Location
    Medan, Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    You are welcome

    Thanks for feed back and also for reb*

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1