+ Reply to Thread
Results 1 to 7 of 7

COUNTIFS excluding duplicates, but other conditions

  1. 06-15-2014, 08:25 PM #1
    kfryar
    kfryar is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    COUNTIFS excluding duplicates, but other conditions

    Hi all,

    I am trying to use a countifs formula to get the number of cells that fall within a certain criteria, excluding duplicate records.

    At the moment my countifs formula looks like this...

    =COUNTIFS('Opportunity Data (Live)'!$H:$H,"won",'Opportunity Data (Live)'!$J:$J,">=01/01/2014",'Opportunity Data (Live)'!$J:$J,"<=01/06/2014)

    But obviously this gives me all the data in those criteria including dupes.

    I need to exclude any duplicate data in 'Opportunity Data (Live)'!$D:$D', this column contains deal names (in text format).

    Please do not suggest pivot tables as this is only part of a bigger picture.

    THANK YOU!
    Reply With QuoteRegister To Reply
  2. 06-15-2014, 08:36 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNTIFS excluding duplicates, but other conditions

    Try this...

    Use cells to hold the criteria:

    A1 = Won
    B1 = 1/1/2014
    C1 = 1/6/2014

    Then, this array formula**...

    =SUM(IF(FREQUENCY(IF(H2:H100=A1,IF(J2:J100>=B1,IF(J2:J100<=C1,MATCH(D2:D100,D2:D100,0)))),ROW(D2:D100)-ROW(D2)+1),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.

    You should avoid using entire columns as range references in array formulas. Use smaller specific ranges.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 06-15-2014, 09:05 PM #3
    kfryar
    kfryar is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: COUNTIFS excluding duplicates, but other conditions

    Thanks Biff,

    This is what i have now but it doesnt seem to work... can't figure out why?!

    =SUM(IF(FREQUENCY(IF('Opportunity Data (Live)'!H2:H2000=Sheet2!B32,IF('Opportunity Data (Live)'!J2:J2000=">="&Sheet2!C21,IF('Opportunity Data (Live)'!J2:J2000="<="&Sheet2!C23,MATCH('Opportunity Data (Live)'!D2:D2000,'Opportunity Data (Live)'!D2:D2000,0)))),ROW('Opportunity Data (Live)'!D2:D2000)-ROW('Opportunity Data (Live)'!D2)+1),1))

    Reply With QuoteRegister To Reply
  4. 06-15-2014, 09:08 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: COUNTIFS excluding duplicates, but other conditions

    Did you use CTRL SHIFT ENTER to enter that?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 06-15-2014, 09:12 PM #5
    kfryar
    kfryar is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: COUNTIFS excluding duplicates, but other conditions

    Yes i did... maybe its something to do with date formatting?
    Reply With QuoteRegister To Reply
  6. 06-15-2014, 09:13 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: COUNTIFS excluding duplicates, but other conditions

    This is what i have now but it doesnt seem to work
    what isnt working?
    Reply With QuoteRegister To Reply
  7. 06-15-2014, 09:34 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNTIFS excluding duplicates, but other conditions

    Quote Originally Posted by kfryar View Post
    Thanks Biff,

    This is what i have now but it doesnt seem to work... can't figure out why?!

    =SUM(IF(FREQUENCY(IF('Opportunity Data (Live)'!H2:H2000=Sheet2!B32,IF('Opportunity Data (Live)'!J2:J2000=">="&Sheet2!C21,IF('Opportunity Data (Live)'!J2:J2000="<="&Sheet2!C23,MATCH('Opportunity Data (Live)'!D2:D2000,'Opportunity Data (Live)'!D2:D2000,0)))),ROW('Opportunity Data (Live)'!D2:D2000)-ROW('Opportunity Data (Live)'!D2)+1),1))

    Change these:

    =">="&Sheet2!C21
    ="<="&Sheet2!C23

    To:

    >=Sheet2!C21
    <=Sheet2!C23
    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)

Similar Threads

  1. [SOLVED] Countifs excluding duplicates
    By Publicpenguin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-10-2014, 09:54 PM
  2. Help with excluding multiple values from a COUNTIFS function.
    By MONEYBALLBINGO in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2014, 10:30 AM
  3. CountIFs results excluding duplicates
    By Excel_learner in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-09-2014, 10:38 AM
  4. [SOLVED] COUNTIFS and excluding duplicates
    By lawend in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-08-2013, 03:39 PM
  5. Countifs results excluding duplicates
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2011, 12:22 PM

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