+ Reply to Thread
Results 1 to 9 of 9

COUNTIFS and Duplicates

  1. 10-03-2012, 03:56 PM #1
    connorwfarrell
    connorwfarrell is offline
    Registered User
    Join Date
    09-17-2012
    Location
    Washington D.C.
    MS-Off Ver
    Excel 2010
    Posts
    4

    COUNTIFS and Duplicates

    I have been having trouble using the COUNTIFS formula to only identify unique terms in a range. Attached is an example of my problem. I have columns for: company name, end date, along with a bunch of other columns with random data and text. I am trying to count the number of unique companies for a given year. In my example, my COUNTIFS statement will currently return a count of 4 for company B in 2012 when I really only want it to be counted once. I have seen similar questions on the forum that give answers involving the FREQUENCY formula, etc. I have not been able to duplicate those formulas for my problem.

    I need a formula that, for a given company name and year, will look at the name (Column B) and the End Date (Column C) and return a unique count.

    Thanks in advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-03-2012, 04:01 PM #2
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: COUNTIFS and Duplicates

    =SUM(IF(FREQUENCY(IF(B$2:B$13="Company "&J2,C$2:C$13),C$2:C$13),1))
    Ctrl+Shift+Enter and not just Enter

    will give you count of unique combination s of Comapny and year

    This will give you a "1" count if the company name and year 2012 combination occurs once or more than once
    =IF(SUMPRODUCT((B2:B13="Company "&J2)*(C2:C13=2012))>=1,1,0)
    Last edited by Ace_XL; 10-03-2012 at 04:09 PM.
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  3. 10-03-2012, 04:20 PM #3
    par0016
    par0016 is offline
    Registered User
    Join Date
    02-01-2012
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: COUNTIFS and Duplicates

    Not sure what format you wanted it but try this:

    Book1(1).xlsx
    Reply With QuoteRegister To Reply
  4. 10-04-2012, 01:04 PM #4
    connorwfarrell
    connorwfarrell is offline
    Registered User
    Join Date
    09-17-2012
    Location
    Washington D.C.
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: COUNTIFS and Duplicates

    Ok, these formulas seemed to work but I don't think I explained exactly what I needed. I need the formula to also take into account the unique text column. Attached is an updated spreadsheet.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 10-04-2012, 01:10 PM #5
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: COUNTIFS and Duplicates

    Why k2=1 AND NOT ZERO?0
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  6. 10-04-2012, 01:12 PM #6
    connorwfarrell
    connorwfarrell is offline
    Registered User
    Join Date
    09-17-2012
    Location
    Washington D.C.
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: COUNTIFS and Duplicates

    K1 refers to to the count of Company A with an End Date of 2012. Two are listed but they have the same unique text so the count should be 1.
    Reply With QuoteRegister To Reply
  7. 10-04-2012, 01:18 PM #7
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: COUNTIFS and Duplicates

    Maybe in E2(helper column-you can hide this) and copy down.

    =COUNTIF($D2:D3,D2)

    Then in K2 and copy down.

    =SUMPRODUCT((B2:B13="Company "&J2)*(C2:C13=2012)*($E$2:$E$13=1))
    Reply With QuoteRegister To Reply
  8. 10-04-2012, 02:06 PM #8
    connorwfarrell
    connorwfarrell is offline
    Registered User
    Join Date
    09-17-2012
    Location
    Washington D.C.
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: COUNTIFS and Duplicates

    This returns a value of 1 in K3 when it should be 2. I don't think the helper column is functioning properly.
    Reply With QuoteRegister To Reply
  9. 10-05-2012, 04:17 AM #9
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: COUNTIFS and Duplicates

    Correct! So try this one in E2 and copy down.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1