+ Reply to Thread
Results 1 to 8 of 8

Count unique values across sheets in a workbook that meet certain conditions

  1. #1
    Registered User
    Join Date
    12-27-2016
    Location
    US
    MS-Off Ver
    2016
    Posts
    2

    Count unique values across sheets in a workbook that meet certain conditions

    I am working with a multi-tabbed workbook (each tab is monthly). For the quarterly tab, I need to count the number of users in from Jan, Feb and Mar that use a specific piece of equipment. If the user uses it 4x in Jan, 3x in Feb and 10x in Mar, the user should only be counted once rather than 17.

    Is it possible to count unique values across multiple sheets in a workbook?

    Thank you.
    Last edited by libramom814; 12-27-2016 at 09:40 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Count unique values across sheets in a workbook that meet certain conditions

    Attach a sample workbook.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    12-27-2016
    Location
    US
    MS-Off Ver
    2016
    Posts
    2

    Re: Count unique values across sheets in a workbook that meet certain conditions

    I've attached a sample file with my comments.

    Thank you.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Count unique values across sheets in a workbook that meet certain conditions

    Welcome to Excel Forum.
    My first inclination was to make a pivot table however the subtotals in the table, columns A:G prevents that.
    See if the attached file is of use to you. Beginning with row 2 column Q, the list of users is populated by the array entered formula*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The list of equipment in L22:L35 is populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The row of user names, M21:V21 is populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The table in M22:V35 is populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Count unique values across sheets in a workbook that meet certain conditions

    A similar approach but using a "helper" tab which has column A as "Users" and row 1 as "workstations"

    in B2

    =SUM(COUNTIFS(INDIRECT("'" &Sheets &"'!b2:b300"),$A2,INDIRECT("'" &Sheets &"'!D2:D300"),B$1))

    Enter with Ctrl+Shift+enter

    Copy across and down

    in B26

    =COUNTIF(B2:B18,">" &0)

    Copy across

    "Sheets" is named range in "Q1": this can be changed to reflect a new quarter BUT Q1 results would also change so maybe have named ranges for each quarter

    OR copy/paste special values in Q1 sheet to "fix" results.

    in tab "Q1"

    in G2

    =INDEX(User_Total,,MATCH($E2,Workstations,0))

    User_Total and Workstations are named ranges
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Count unique values across sheets in a workbook that meet certain conditions

    John's approach is GREAT and I thought that I might throw in a few things to make it more automated. Notice on the 'Helper' tab there is a table in columns R:T that will collect the users from the three monthly sheets. Changing the months in the sheets table, on the Q1 tab, will automatically change them in R2:T2. Column A, on the 'Helper' tab, compiles a unique list of users from the three monthly list. There is a similar table in columns W:Z for the workstations in the event that you should ever want to list only those that were used during the quarter.
    The user count in column G, on tab 'Q1', works the same as it did, however the list of users which was in column I only, now takes up columns I:R and fills automatically using the array entered formula (see *explanation in post #4):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Count unique values across sheets in a workbook that meet certain conditions

    @JeteMc: appreciate the "upgrade"!!!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Count unique values across sheets in a workbook that meet certain conditions

    @JohnTopley: You're Welcome. The work you did inspired me!

+ 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. Count Unique Values that Meet a Criteria
    By DD1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-09-2016, 11:23 PM
  2. How to count unique values in two ranges that meet conditions
    By akbermo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-18-2015, 08:22 AM
  3. Count number of contiguous values in a range which meet conditions
    By LAUGHINGCROW in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-15-2014, 09:25 AM
  4. [SOLVED] Count records that meet mutiple conditions in multiple sheets
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2012, 06:56 AM
  5. Replies: 25
    Last Post: 05-31-2012, 08:03 PM
  6. Replies: 5
    Last Post: 03-13-2012, 06:05 AM
  7. Replies: 6
    Last Post: 01-17-2010, 07:30 PM

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