+ Reply to Thread
Results 1 to 6 of 6

Count only unique cells in column where another cell in row is not blank

  1. #1
    Registered User
    Join Date
    03-22-2015
    Location
    LAs Vegas, NV
    MS-Off Ver
    2010
    Posts
    13

    Count only unique cells in column where another cell in row is not blank

    I want to count the unique cells in Column A That have a number in Column C and actually has a dollar amount greater than $0.00.

    A B C D
    C20358 Carriage House; The 479220 $84.11*
    C20449 Historic Tamarack Lodge 481341 $29.97*
    C20449 Historic Tamarack Lodge 520668 $9.54*
    C20457 Old Forge Camping Resort 480541 $176.04*
    C20457 Old Forge Camping Resort 480591 $0.00
    C399. MCD. $0.00



    The answer should be 3

    C20358
    C20449
    C20457

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Count only unique cells in column where another cell in row is not blank

    e25 =if(and(C25<>"",D25>0),"yes","no")

    and drag down.

    After that a pivot table.

    C number name number value criteria
    C20358 CarriageHouse;The 479220 84,11 yes
    C20449 HistoricTamarackLodge 481341 29,97 yes
    C20449 HistoricTamarackLodge 520668 9,54 yes
    C20457 OldForgeCamping Resort 480541 176,04 yes
    C20457 OldForgeCamping Resort 480591 0 no
    C399. MCD 0 no


    criteria yes

    Rijlabels Unieke telling van name
    C20358 1
    C20449 1
    C20457 1
    Eindtotaal 3
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    07-13-2015
    Location
    Poland
    MS-Off Ver
    MS Office 365
    Posts
    171

    Re: Count only unique cells in column where another cell in row is not blank

    Please Login or Register  to view this content.

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

    Re: Count only unique cells in column where another cell in row is not blank

    With your above sample data in Range A2:D7 use this array formula

    =SUM(IF(FREQUENCY(IF((D2:D7>0)*ISNUMBER(C2:C7),MATCH(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)),1,0))

    Confirm with Ctrl+Shift+Enter
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Registered User
    Join Date
    03-22-2015
    Location
    LAs Vegas, NV
    MS-Off Ver
    2010
    Posts
    13

    Re: Count only unique cells in column where another cell in row is not blank

    Thanks Ace XL..I believe I just need one more step... some rows will have "N/A" showing up in column D that I would like to ignore and treat as if it was $0

  6. #6
    Registered User
    Join Date
    03-22-2015
    Location
    LAs Vegas, NV
    MS-Off Ver
    2010
    Posts
    13

    Re: Count only unique cells in column where another cell in row is not blank

    Thanks Ace XL..I believe I just need one more step... some rows will have "N/A" showing up in column D that I would like to ignore and treat as if it was $0

+ 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 only unique cells in column where another cell in row is not blank
    By accounting2015 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-12-2015, 06:18 PM
  2. Replies: 3
    Last Post: 07-12-2015, 03:37 PM
  3. [SOLVED] Count Unique Values if Adjacent Cell is Not Blank
    By Dr. D in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-18-2015, 03:05 PM
  4. [SOLVED] Assigning a unique value to all blank cells in a column
    By mohammedfaizal in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-14-2013, 09:41 AM
  5. [SOLVED] Need to Count Blank cells from active cell going UP the same column
    By EDaig in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2013, 11:22 PM
  6. Replies: 2
    Last Post: 10-04-2012, 03:19 PM
  7. 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