+ Reply to Thread
Results 1 to 6 of 6

Count Unique Values if Adjacent Cell is Not Blank

  1. #1
    Registered User
    Join Date
    07-07-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    5

    Count Unique Values if Adjacent Cell is Not Blank

    Ok, so here is an example of my worksheet.
    Untitled.png

    I am currently using the following formula to count the unique number of dates in column A (displayed in E5).
    =SUMPRODUCT((A2:A5<>"")/COUNTIF(A2:A5,A2:A5&""))

    I would like to make a change so the formula in E5 will only count days that have text (or are not blank) in column C of same row (so it's not counting days not worked yet).
    I can't think of a good way to do this. Any ideas?

    Thanks!

  2. #2
    Registered User
    Join Date
    07-07-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Count Unique Values if Adjacent Cell is Not Blank

    I thought I had it figured out, but it's having issues when there are duplicate dates and a different value in column C for each date.
    I've attached an example worksheet if anyone can help.

    Example.xlsx

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count Unique Values if Adjacent Cell is Not Blank

    Try this array formula**:

    =SUM(IF(FREQUENCY(IF(C2:C10<>"",A2:A10),A2:A10),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: Count Unique Values if Adjacent Cell is Not Blank

    Can you tell us what your expected results are?
    Glenn



  5. #5
    Registered User
    Join Date
    07-07-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Count Unique Values if Adjacent Cell is Not Blank

    Thanks, Tony! That seems to work.

    Glenn,

    The expected results for the example sheet are as follows:

    If all cells in column C have values, the days worked should be 5. Each date should only be counted once, so if there are no values in C2 or C3, the result should be 4. Make sense? I was trying to use the formula in cell E6, but I couldn't figure out how to make it stop returning 0.5 for days in there twice, etc.

    Thanks, all!

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count Unique Values if Adjacent Cell is Not Blank

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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 unique values if adjacent cell meets criteria
    By chococ in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-01-2014, 04:13 AM
  2. [SOLVED] Count Unique Values in a Column that are not in adjacent Column
    By sskgintl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2013, 03:18 PM
  3. Replies: 4
    Last Post: 09-12-2013, 11:32 PM
  4. Replies: 2
    Last Post: 08-17-2011, 01:35 PM
  5. total count of non-blank cell(s) and unique values
    By esanchezz in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-09-2010, 01:53 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