+ Reply to Thread
Results 1 to 7 of 7

Count Distinct Values OFFSET

  1. #1
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    175

    Count Distinct Values OFFSET

    I need to count the number of Distinct entries in two columns. (table is attached)

    Column A has sorted account numbers. There are repeated entries.
    Column B has a 'y' or an 'n'.

    I need a formula to enter in my spreadsheet to keep the count up-to-date as entries are made.

    The three separate formulas will be:
    - Total DISTINCT Entries:
    - Total DISTINCT Entries with an 'n':
    - Total DISTINCT Entries with a 'y':

    Obviously, for a value to be Distinct, it will only count the first instance of each instance of [Col-A and Col-B], meaning that at the most there could be one 'n' and one 'y' with each account number in Col-A.

    The attachment shows the sample data and the desired results. I just need the formulas to put in E5,E7,E9 to do this for me, without having to manually create a filter every time I want an update.

    Any assistance is greatly appreciated. I have found lots of similar problems but none that I could actually use to work this out. Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Count Distinct Values OFFSET

    A work in progress, but here's your Total Distinct formula:

    =COUNT(1/FREQUENCY(IF(A1:A100&B1:B100<>"",MATCH(A1:A100&B1:B100,A1:A100&B1:B100,0)),ROW(A1:A100)-ROW(A1)+1))

    This formula needs to be entered as an array using Ctrl+Shift+Enter. I totally got this from NBVC. https://www.excelforum.com/showthread.php?t=837655

    I will continue testing out the others.
    Last edited by daffodil11; 08-20-2013 at 01:46 PM. Reason: CREDIT TO NBVC

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Count Distinct Values OFFSET

    And here's your distinct n:

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


    And distinct y:

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


    Both also entered as arrays using Ctrl+Shift+Enter

    I found this one by Siddharth Rout

    http://stackoverflow.com/questions/1...th-a-condition

  4. #4
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    175

    Re: Count Distinct Values OFFSET

    Thank you so much . . . this is great. I'm really gonna study it to figure out why I couldn't figure out how to do this.
    One last thing - I searched for this before posting and couldn't find it - so obviously, in addition to my lacking Excel skills, my search skills need some work.
    By what method did you search for and find this work by Siddharth Rout?

    Again, thanks a million. I'll be glad when one day I know enough to contribute likewise.

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Count Distinct Values OFFSET

    I think I landed on NBVC by Googling excel count unique combinations and I hit Siddhart Rout with excel count unique rows with a condition.

    Sometimes the hardest part is asking the right question.

  6. #6
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    175

    Re: Count Distinct Values OFFSET

    Thanks for the help . . . I learn a lot from this forum.
    You get a full 6-star rating! Have a great day!

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Count Distinct Values OFFSET

    I learn at least as much as I try to teach.

+ 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 Conditional Distinct Values
    By Gos-C in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2013, 05:41 AM
  2. [SOLVED] Count Distinct Values
    By djfatboyfats in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-20-2012, 07:39 AM
  3. count two columns for distinct values
    By warrencameron in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-18-2011, 09:09 AM
  4. Count distinct values with criteria
    By greencardioid in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-29-2008, 06:48 PM
  5. [SOLVED] Count Distinct Values?
    By bill_morgan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-26-2005, 10:06 PM

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