Results 1 to 9 of 9

Counting Non-Duplicate Cells IF

This is a discussion on Counting Non-Duplicate Cells IF within the Excel Questions forums, part of the Question Forums category; Hi Guys, I originally posted the other day here > http://www.mrexcel.com/forum/showthread.php?t=369858 however after much head banging I thought it best ...

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Location
    Edinburgh
    Posts
    217

    Default Counting Non-Duplicate Cells IF

    Hi Guys,

    I originally posted the other day here > Counting Non-Duplicate Cells however after much head banging I thought it best to start again and reach out for some help, again.

    The Situation Now
    I have a sheet containing all my data, 'Closed Projects', and a sheet containing all my stats, 'Stats'.

    I want to look at two columns on 'Closed Projects', Col.BL and Col.BO (All relevant data is contained in Row 4 and beneath if that matters).

    I want to count the number of rows where:
    1. Col.BL = 'Stats'!$B$4
    2. Col.BO = is unique compare to other values in the column - ie if there is multiple occurrences of the same number I only count one. I donít want to count blank cells. (The data in Col.BO will be a name or number). Iíve tried using DCOUNTA but couldnít get it working.

    Note on point 2: the value returned would ideally be 4, using this sample data:
    1. 56655
    2. 45412
    3. 45412
    4.

    5. 36441
    6.
    7. 56655

    8. 44888

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,252

    Default Re: Counting Non-Duplicate Cells IF

    Try...

    Code:
    =SUM(IF(FREQUENCY(IF('Closed Projects'!$BL$2:$BL$100='Stats'!$B$4,IF('Closed Projects'!$BO$2:$BO$100<>"",MATCH("~"&'Closed Projects'!$BO$2:$BO$100,'Closed Projects'!$BO$2:$BO$100&"",0))),ROW('Closed Projects'!$BO$2:$BO$100)-ROW('Closed Projects'!$BO$2)+1),1))
    ...confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

  3. #3
    Board Regular
    Join Date
    Feb 2008
    Location
    Edinburgh
    Posts
    217

    Default Re: Counting Non-Duplicate Cells IF

    Marvellous



    Many Many Many thanks.


    Ive one more question, however I may be better asking on a new thread.

    In the formula you provided, the rows are limited to the first 100 rows - thats fine, I can extend that by makeing the 'BL$100', 'BL$200' and so on - right? However, I have several formulas that already have similar problems.

    If there anyway I can create a cell that would hold the number of the last row with data in it? That way I could make some of the forumlas relative to the last row of data rather than a random number - which one day will fall over. New rows are added to the sheet weekly, always at the bottom below the current last row.

    Any ideas?
    Last edited by The doomed; Feb 11th, 2009 at 08:44 AM.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,252

    Default Re: Counting Non-Duplicate Cells IF

    Quote Originally Posted by The doomed View Post
    Marvellous



    Many Many Many thanks.
    You're very welcome!


    Ive one more question, however I may be better asking on a new thread.

    In the formula you provided, the rows are limited to the first 100 rows - thats fine, I can extend that by makeing the 'BL$100', 'BL$200' and so on - right? However, I have several formulas that already have similar problems.

    If there anyway I can create a cell that would hold the number of the last row with data in it? That way I could make some of the forumlas relative to the last row of data rather than a random number - which one day will fall over. New rows are added to the sheet weekly, always at the bottom below the current last row.

    Any ideas?
    Which version of Excel are you using?

  5. #5
    Board Regular
    Join Date
    Feb 2008
    Location
    Edinburgh
    Posts
    217

    Default Re: Counting Non-Duplicate Cells IF

    2003

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,252

    Default Re: Counting Non-Duplicate Cells IF

    Convert your data into a list...

    Data > List > Create List

    The range will automatically adjust as data is added/removed.

  7. #7
    Board Regular
    Join Date
    Feb 2008
    Location
    Edinburgh
    Posts
    217

    Default Re: Counting Non-Duplicate Cells IF

    Getting problem due to autofilters on sheet - however I need them.

    If there's something else straight forward then great, if not, I'm happy enough to leave it.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,103

    Default Re: Counting Non-Duplicate Cells IF

    Quote Originally Posted by The doomed View Post
    Getting problem due to autofilters on sheet - however I need them.

    If there's something else straight forward then great, if not, I'm happy enough to leave it.
    Remove AutoFilter, then create the list. Once created, the lists allow filtering

  9. #9
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,915

    Default Re: Counting Non-Duplicate Cells IF

    The formula
    =SUMPRODUCT(--(BL4:BL1000=Stats!B4),1/COUNTIF(BO4:BO1000,BO4:BO1000&""))
    will return the number that you want

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com