+ Reply to Thread
Results 1 to 12 of 12

Help Needed on COUNTIFS to count non duplicates

  1. 08-10-2016, 06:43 AM #1
    alex1984
    alex1984 is offline
    Registered User
    Join Date
    08-10-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    5

    Help Needed on COUNTIFS to count non duplicates

    Capture.PNGCapture.PNG


    I need a countifs formula to return that "Alex J" only gets counted once for Line 2 as "direct" for the Day and for the provider PF. Presently he appears twice but only want him counted once.

    The line can change as can the Day as can the direct/indirect...please help

    Thanks
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 08-10-2016, 06:45 AM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help Needed on COUNTIFS to count non duplicates

    Hi and welcome to the forum.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely much use since not many of us want to spend time recreating something you already have.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 08-10-2016, 06:47 AM #3
    alex1984
    alex1984 is offline
    Registered User
    Join Date
    08-10-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    5

    Re: Help Needed on COUNTIFS to count non duplicates

    \1
    Reply With QuoteRegister To Reply
  4. 08-10-2016, 06:48 AM #4
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help Needed on COUNTIFS to count non duplicates

    ...that's a picture, and very small too.

    Please upload the workbook.
    Reply With QuoteRegister To Reply
  5. 08-10-2016, 06:49 AM #5
    alex1984
    alex1984 is offline
    Registered User
    Join Date
    08-10-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    5

    Re: Help Needed on COUNTIFS to count non duplicates

    how to do I upload the workbook?
    Reply With QuoteRegister To Reply
  6. 08-10-2016, 06:56 AM #6
    sanram
    sanram is offline
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Help Needed on COUNTIFS to count non duplicates

    Click on Go Advance, then manage attachment, choose file, then click on upload, then click on close the window, then post reply. That's it.
    Reply With QuoteRegister To Reply
  7. 08-10-2016, 06:56 AM #7
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help Needed on COUNTIFS to count non duplicates

    Quote Originally Posted by alex1984 View Post
    how to do I upload the workbook?
    It's in the FAQ menu.

    http://www.excelforum.com/faq.php?fa...chment_explain
    Reply With QuoteRegister To Reply
  8. 08-10-2016, 07:01 AM #8
    alex1984
    alex1984 is offline
    Registered User
    Join Date
    08-10-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    5

    Re: Help Needed on COUNTIFS to count non duplicates

    with attachment
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 08-10-2016, 07:12 AM #9
    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: Help Needed on COUNTIFS to count non duplicates

    There is not a duplicate entry for Alex J, PF, Direct, 2.

    =COUNTIFS(A2:A9,"Alex J",B2:B9,"PF",D2:D9,"Direct",E2:E9,2) = 1
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  10. 08-10-2016, 07:19 AM #10
    sanram
    sanram is offline
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Help Needed on COUNTIFS to count non duplicates

    Write the following formula in cell G2 :

    Please Login or Register  to view this content.
    Now copy G2 to G3:G9.
    Reply With QuoteRegister To Reply
  11. 08-10-2016, 07:25 AM #11
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help Needed on COUNTIFS to count non duplicates

    ...or why not use a Pivot Table which would be more flexible. See attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 08-10-2016, 08:15 AM #12
    alex1984
    alex1984 is offline
    Registered User
    Join Date
    08-10-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    5

    Re: Help Needed on COUNTIFS to count non duplicates

    many thanks
    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. Sum/Count/Average based on multi criteria in table, skip duplicates when not needed.
    By blak9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-01-2015, 11:09 AM
  2. [SOLVED] Countifs (2 critera) but only once and do not count duplicates.
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-26-2015, 08:10 PM
  3. [SOLVED] Countifs Formula needed to count # of dates in same month that also is in same state.
    By Stanimal in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-12-2014, 02:43 PM
  4. [SOLVED] COUNTIFS without duplicates
    By amphinomos in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-12-2014, 11:08 AM
  5. Using COUNTIFS with multiple ranges & criteria...how to count unique / exclude duplicates?
    By researchcibc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-04-2014, 10:56 AM
  6. COUNTIFS and Duplicates
    By connorwfarrell in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-05-2012, 04:17 AM
  7. Remove duplicates and count their number - faster method needed
    By ciprian in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-02-2011, 08:24 AM

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