+ Reply to Thread
Results 1 to 5 of 5

Count unique over multiple sheets.

  1. #1
    Registered User
    Join Date
    08-03-2018
    Location
    Pittsburgh
    MS-Off Ver
    Excel for Mac 16.15
    Posts
    1

    Question Count unique over multiple sheets.

    I am trying to count the number of unique entries in a column over multiple sheets. For example...

    SHEET 1
    Apple
    Banana
    Pear

    SHEET 2
    Apple
    Apple
    Apple

    SHEET 3
    Banana
    Cheeseburger
    Pear

    The count I'm looking to get would be 4 beause there are 4 total unique items - Apple, Banana, Pear, Cheeseburger.

    Thanks!

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,886

    Re: Count unique over multiple sheets.

    Firstly define sheet name.
    in summary sheet in "F2"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    "E2" is criteria.
    see attach file.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,534

    Re: Count unique over multiple sheets.

    A bit different approach as I use a macro to find the unique values. To test click macro button "Run Macro" on "Summary" sheet. I've also added more items to the different sheets to test the range setting.

    Alf
    Attached Files Attached Files
    Last edited by Alf; 08-05-2018 at 09:34 AM.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,817

    Re: Count unique over multiple sheets.

    Here's a formula that "works".

    Unfortunately I was unable to come up with something shorter or simpler. I had to settle for atrocious. LOL

    It is array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,817

    Re: Count unique over multiple sheets.

    Ah! Managed to trim that previous formula down a bit ... from 4 and half lines to 2 and a third. Still array entered.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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 across sheets in a workbook that meet certain conditions
    By libramom814 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-29-2016, 08:41 AM
  2. [SOLVED] Sort and Count Unique List Across Multiple Columns - Student Count
    By BuntyMac in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 08-17-2014, 07:00 AM
  3. [SOLVED] Needing to count unique values across multiple sheets
    By TheTubaGeek in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-10-2014, 09:50 AM
  4. [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
  5. count unique values in multiple worksheets with multiple criteria
    By craig04 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-06-2012, 03:26 PM
  6. Replies: 8
    Last Post: 07-13-2012, 06:00 AM
  7. 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