+ Reply to Thread
Results 1 to 15 of 15

Needing to count unique values across multiple sheets

  1. #1
    Registered User
    Join Date
    06-10-2014
    Location
    Concord, NC
    MS-Off Ver
    2013
    Posts
    24

    Angry Needing to count unique values across multiple sheets

    I have a workbook setup with 6 sheets: 5 for data and one that calculates the aggregate results.

    In each worksheet I have a column that has a list of numbers. Sometimes, there are multiple instances of the same number across multiple worksheets, which skews the calculations. I have a formula for the individual worksheets already that counts unique values on each individual worksheet:

    =SUMPRODUCT((C2:C102<>"")/COUNTIF(C2:C102,C2:C102&""))

    I would just like to get something that does this across all of my worksheets. Any advice?

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Needing to count unique values across multiple sheets

    Hi,

    If you post an attachment with an example and your desired result then I'm sure we'll be able to help.

    Regards
    Click * below if this answer helped

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

  3. #3
    Registered User
    Join Date
    06-10-2014
    Location
    Concord, NC
    MS-Off Ver
    2013
    Posts
    24

    Re: Needing to count unique values across multiple sheets

    Here you go!

    Time Tracking.xlsx

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Needing to count unique values across multiple sheets

    Quote Originally Posted by TheTubaGeek View Post

    =SUMPRODUCT((C2:C102<>"")/COUNTIF(C2:C102,C2:C102&""))
    For counting unique numbers, this is more efficient:

    =SUM(--(FREQUENCY(C2:C102,C2:C102)>0))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Needing to count unique values across multiple sheets

    Thanks. So are the values you've entered in the Aggregate sheet your expected results?

    If so, what's wrong with the current set-up you have?

    If not, could you please add what the actual results should be?

    Regards

  6. #6
    Registered User
    Join Date
    06-10-2014
    Location
    Concord, NC
    MS-Off Ver
    2013
    Posts
    24

    Re: Needing to count unique values across multiple sheets

    The Aggregate just calculates the values for the entirety of the week.

    The problem is that the FCR and Time per Incident values are skewed because the Tickets Handled value could (and more often than not, will), count instances where the same Incident # is listed across multiple worksheets.

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Needing to count unique values across multiple sheets

    Sure. But I only hoped that you'd add your manually-calculated expected results in there, so at least we know what we're aiming for as a final result.

    Regards

  8. #8
    Registered User
    Join Date
    06-10-2014
    Location
    Concord, NC
    MS-Off Ver
    2013
    Posts
    24

    Re: Needing to count unique values across multiple sheets

    There is no expected result, except for the results expected by management that I guess I could put in there. I am more using this as a personal tracking system, though.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Needing to count unique values across multiple sheets

    I didn't download your file...

    Sheet1
    A
    1
    1
    2
    2
    3
    3
    4
    4
    5
    5



    Sheet2
    A
    1
    1
    2
    2
    3
    7
    4
    4
    5
    6


    Count of unique numbers across the sheets:

    =SUM(--(FREQUENCY(Sheet1:Sheet2!A1:A5,Sheet1:Sheet2!A1:A5)>0))

    Result = 7

  10. #10
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Needing to count unique values across multiple sheets

    How can there be no expected result?!

    You mean with the current values in there you can't even say what the results in your Aggregate sheet should be?

    If you can't, not sure I can help!

    Regards
    Last edited by XOR LX; 06-10-2014 at 09:39 AM.

  11. #11
    Registered User
    Join Date
    06-10-2014
    Location
    Concord, NC
    MS-Off Ver
    2013
    Posts
    24

    Re: Needing to count unique values across multiple sheets

    Sorry, I didn't follow your meaning.

    The Aggregate Tickets Handled should be the total count of unique instances of each ticket number entered across all sheets. Previously, I had it as a pure aggregate where I was adding the Tickets Handled value in each sheet; however, that is not a true value of how many tickets I handled in a week, as sometimes I have to address the same Ticket Number on multiple days

  12. #12
    Registered User
    Join Date
    06-10-2014
    Location
    Concord, NC
    MS-Off Ver
    2013
    Posts
    24

    Re: Needing to count unique values across multiple sheets

    Tony, I tried that and get #NAME? for the result. Do I need to change the names of my worksheets?

    EDIT: Figured out what I was doing wrong, and that got it working. Thanks!
    Last edited by TheTubaGeek; 06-10-2014 at 09:45 AM.

  13. #13
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Needing to count unique values across multiple sheets

    Quote Originally Posted by TheTubaGeek View Post
    Sorry, I didn't follow your meaning.

    The Aggregate Tickets Handled should be the total count of unique instances of each ticket number entered across all sheets. Previously, I had it as a pure aggregate where I was adding the Tickets Handled value in each sheet; however, that is not a true value of how many tickets I handled in a week, as sometimes I have to address the same Ticket Number on multiple days
    Ok, then Tony's method is indeed what you need.

    Regards

  14. #14
    Registered User
    Join Date
    06-10-2014
    Location
    Concord, NC
    MS-Off Ver
    2013
    Posts
    24

    Re: Needing to count unique values across multiple sheets

    How do I mark this solved?

  15. #15
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Needing to count unique values across multiple sheets

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] count unique values across multiple sheets based on date
    By roninn75 in forum Excel General
    Replies: 8
    Last Post: 02-25-2014, 05:20 PM
  2. Replies: 8
    Last Post: 07-13-2012, 06:00 AM
  3. Replies: 16
    Last Post: 01-11-2012, 10:51 AM
  4. Replies: 5
    Last Post: 04-21-2011, 05:22 PM
  5. Count unique logs with multiple conditions of multiple sheets
    By Robert Bob in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-12-2007, 12:49 AM

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