+ Reply to Thread
Results 1 to 8 of 8

Count unique values if adjacent cell meets criteria

  1. #1
    Registered User
    Join Date
    01-14-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    45

    Count unique values if adjacent cell meets criteria

    Hi all,

    I need a little help with perfecting my formula. In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.
    Capture.PNG

    At the moment I have a formula as below:

    =SUMPRODUCT(--(B:B=$D2),--(A:A<>""))

    this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.

    I need some help modifying my formula, or ideas on a solution. I have attached the sample workbook below for your convenience.

    Book1.xlsx

    Any help is very much appreciated in advance.

    Many thanks.
    Last edited by chococ; 04-30-2014 at 02:25 PM.

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Count unique values if adjacent cell meets criteria

    Not sure if your results are as per expectation

    There are 4 unique values for 666 and 2 for 777

    Try
    =SUMPRODUCT(($B$2:$B$10=D2)/COUNTIFS($A$2:$A$10,$A$2:$A$10&"",$B$2:$B$10,$B$2:$B$10&""))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    01-14-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Count unique values if adjacent cell meets criteria

    Sorry my bad, yes should be 4 for 666 but 1 for 777 because I would like to ignore blanks in column A. Is that possible? Thanks for your reply!

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Count unique values if adjacent cell meets criteria

    Try

    =SUMPRODUCT(($A$2:$A$10<>"")*($B$2:$B$10=D2)/COUNTIFS($A$2:$A$10,$A$2:$A$10&"",$B$2:$B$10,$B$2:$B$10&""))

  5. #5
    Registered User
    Join Date
    01-14-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Count unique values if adjacent cell meets criteria

    Perfect! Thanks a lot!!! I'll mark this thread as solved and add a reputation to you.

  6. #6
    Registered User
    Join Date
    01-14-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Count unique values if adjacent cell meets criteria

    Quick question if you are still online: What does the &"" do in the COUNTIFS? I can understand first half of the formula you wrote. Would be great if I know how it works!

  7. #7
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Count unique values if adjacent cell meets criteria

    It ensures the blank cells do not give you a #DIV/0 error

  8. #8
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Count unique values if adjacent cell meets criteria

    ...deleted

+ 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. Replies: 4
    Last Post: 02-17-2014, 04:05 AM
  2. [SOLVED] Count values in a range if adjacent cell meets a particular criteria
    By DougC in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-22-2013, 03:12 PM
  3. Sum Up Cells Until Adjacent Cell Meets Criteria
    By cocostar88 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-27-2013, 02:41 PM
  4. Count unique values in a column for each row that meets 3 criteria
    By xtomg19 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-18-2012, 12:15 AM
  5. [SOLVED] Add a range of values when an adjacent cell meets criteria
    By Steven811 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-19-2012, 06:04 AM

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