+ Reply to Thread
Results 1 to 6 of 6

Counting distinct values in relation to text values in a separate coulumn

  1. #1
    Registered User
    Join Date
    04-05-2019
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    2

    Counting distinct values in relation to text values in a separate coulumn

    Hi there everyone,

    Hope somebody can help, I'm a bit of an Excel novice and can't get my head around this one.

    I have a list of alphanumeric figures in column A, and a numeric value in column B. I need to count how many figures in column B are not equal to 0, but I only want to count them once per value in column A. Hope that makes sense, I have (hopefully) attached a short example. The value I am looking for the formula to return is 3, because 16R008A appears twice.

    I have managed to create a formula just to count the unique values in A, is there anyway of modifying this to help with my query?

    =SUM(IF(FREQUENCY(MATCH(WH16,WH16,0),MATCH(WH16,WH16,0))>0,1))

    WH16 is the name range for my data in column A.

    Would appreciate any help, thanks for your time.

    Chris
    Attached Files Attached Files
    Last edited by Streatty; 04-05-2019 at 05:10 PM. Reason: Invalid result!

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,582

    Re: Counting distinct values in relation to text values in a separate coulumn

    Welcome to the Board; one option

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

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,582

    Re: Counting distinct values in relation to text values in a separate coulumn

    edit: ignore the above as would fail if first instance for given value in A was 0

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

  4. #4
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Counting distinct values in relation to text values in a separate coulumn

    =sumproduct((b2:b12<>0)*(match(a2:a12,a2:a12,)=row(a2:a12)-1)*1)

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,045

    Re: Counting distinct values in relation to text values in a separate coulumn

    Please try this and press Ctrl+Shift+Enter

    =SUM(--(FREQUENCY(IF(B2:B14,MATCH(A2:A14,A2:A14,)),ROW(A1:A14))>0))

  6. #6
    Registered User
    Join Date
    04-05-2019
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Counting distinct values in relation to text values in a separate coulumn

    AMAZING!!!!

    Thanks for your help everybody, loads of options to play with here. Thanks for your time.

+ 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. Counting distinct values in Excel
    By ca16 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-26-2016, 12:42 PM
  2. Replies: 3
    Last Post: 10-28-2015, 06:16 AM
  3. Replies: 0
    Last Post: 04-27-2015, 03:32 PM
  4. [SOLVED] Counting distinct values with blank cells produces #DIV/0!
    By antexity in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-14-2015, 09:00 AM
  5. [SOLVED] counting unique/distinct values (sample data attached)
    By justinhampton81 in forum Excel General
    Replies: 9
    Last Post: 08-06-2014, 12:41 AM
  6. Replies: 2
    Last Post: 10-05-2011, 12:30 PM
  7. Counting Distinct Values
    By giantwolf in forum Excel General
    Replies: 4
    Last Post: 12-29-2005, 11:03 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