+ Reply to Thread
Results 1 to 15 of 15

Needing to count unique values across multiple sheets

  1. 06-10-2014, 09:05 AM #1
    TheTubaGeek
    TheTubaGeek is offline
    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?
    Reply With QuoteRegister To Reply
  2. 06-10-2014, 09:11 AM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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/
    Reply With QuoteRegister To Reply
  3. 06-10-2014, 09:17 AM #3
    TheTubaGeek
    TheTubaGeek is offline
    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
    Reply With QuoteRegister To Reply
  4. 06-10-2014, 09:20 AM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  5. 06-10-2014, 09:22 AM #5
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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
    Reply With QuoteRegister To Reply
  6. 06-10-2014, 09:29 AM #6
    TheTubaGeek
    TheTubaGeek is offline
    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.
    Reply With QuoteRegister To Reply
  7. 06-10-2014, 09:30 AM #7
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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
    Reply With QuoteRegister To Reply
  8. 06-10-2014, 09:33 AM #8
    TheTubaGeek
    TheTubaGeek is offline
    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.
    Reply With QuoteRegister To Reply
  9. 06-10-2014, 09:36 AM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert 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
    Reply With QuoteRegister To Reply
  10. 06-10-2014, 09:36 AM #10
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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.
    Reply With QuoteRegister To Reply
  11. 06-10-2014, 09:40 AM #11
    TheTubaGeek
    TheTubaGeek is offline
    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
    Reply With QuoteRegister To Reply
  12. 06-10-2014, 09:43 AM #12
    TheTubaGeek
    TheTubaGeek is offline
    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.
    Reply With QuoteRegister To Reply
  13. 06-10-2014, 09:46 AM #13
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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
    Reply With QuoteRegister To Reply
  14. 06-10-2014, 09:48 AM #14
    TheTubaGeek
    TheTubaGeek is offline
    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?
    Reply With QuoteRegister To Reply
  15. 06-10-2014, 09:50 AM #15
    Tony Valko
    Tony Valko is offline
    Forum Expert 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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. [SOLVED] Count unique values once with multiple criteria across multiple sheets
    By bteo in forum Excel General
    Replies: 8
    Last Post: 07-13-2012, 06:00 AM
  3. Count unique values in three columns depending on multiple values
    By lainey76 in forum Excel General
    Replies: 16
    Last Post: 01-11-2012, 10:51 AM
  4. Count unique values in one column depending on multiple values in multiple columns
    By redraishes in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1