+ Reply to Thread
Results 1 to 11 of 11

CountIFS without duplicate

  1. #1
    Registered User
    Join Date
    01-24-2015
    Location
    Singapore
    MS-Off Ver
    office365
    Posts
    16

    CountIFS without duplicate

    Dear All, I am facing a problem with how to get CountIFS without duplicate,

    example:

    04/Mar/15 RV1503-0001
    04/Mar/15 RV1503-0002
    04/Mar/15 RV1503-0003
    04/Mar/15 RV1503-0004
    04/Jan/15 RV1503-0005
    05/Mar/15 RV1503-0006
    06/Mar/15 RV1503-0007
    04/Jan/15 RV1503-0008
    04/Jan/15 RV1503-0009
    04/Mar/15 RV1503-0010
    04/Mar/15 RV1503-0011
    04/Mar/15 RV1503-0011
    04/Mar/15 RV1503-0011
    04/Mar/15 RV1503-0011
    04/Mar/15 RV1503-0011
    04/Mar/15 RV1503-0011
    04/Mar/15 RV1503-0011


    first Criteria is the date within the month of H1
    and to count those in col D with "*RV*"

    I can do half way. those duplicate one have no idea... ... ...

    thank you so much for your kindly help
    2) CountIFS without duplicate.xlsb
    Please refer to file attached.
    Last edited by AndyLiu; 03-04-2015 at 11:14 AM. Reason: Solved

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: CountIFS without duplicate

    you need to create a column for unique numbers fist then it can be possible

    pls check the attachment

    Hope it will help you
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: CountIFS without duplicate

    option, without COUNTIFS
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-24-2015
    Location
    Singapore
    MS-Off Ver
    office365
    Posts
    16

    Re: CountIFS without duplicate

    =SUMPRODUCT((A1:A17>=DATE(YEAR($H$1),MONTH($H$1),1))*(A1:A17<=DATE(YEAR($H$1),MONTH($H$1)+1,0))*(COUNTIF(D1:D17,D1:D17)=1)*1)

    that is awsome ! thank you so much.

    Just a bit further, range D1:D17 might contains of "*PV*" or "*RV*", how shall i adjust your formula to put specific value i want as criteria ?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-24-2015
    Location
    Singapore
    MS-Off Ver
    office365
    Posts
    16

    Re: CountIFS without duplicate

    Quote Originally Posted by shukla.ankur281190 View Post
    you need to create a column for unique numbers fist then it can be possible

    pls check the attachment

    Hope it will help you
    Thank you so much, it helps !

  6. #6
    Registered User
    Join Date
    01-24-2015
    Location
    Singapore
    MS-Off Ver
    office365
    Posts
    16

    Re: CountIFS without duplicate

    Quote Originally Posted by shukla.ankur281190 View Post
    you need to create a column for unique numbers fist then it can be possible

    pls check the attachment

    Hope it will help you
    Thank you so much, it helps !

    alternative method by watersev is awesome, just i can not figure out how to put specific text as criteria.

  7. #7
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: CountIFS without duplicate

    please check if that is OK with you
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-24-2015
    Location
    Singapore
    MS-Off Ver
    office365
    Posts
    16

    Re: CountIFS without duplicate

    Quote Originally Posted by watersev View Post
    please check if that is OK with you
    that is absolutely what i want !! really awesome !! thank you so much !!!

  9. #9
    Registered User
    Join Date
    01-24-2015
    Location
    Singapore
    MS-Off Ver
    office365
    Posts
    16

    Re: CountIFS without duplicate

    Quote Originally Posted by watersev View Post
    please check if that is OK with you
    Hi Watersev,

    thanks again for your help, I just released your formula ignore those duplicated one, how could I include it but consider as one item to add in the sum ?

  10. #10
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: CountIFS without duplicate

    please check attachment
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-24-2015
    Location
    Singapore
    MS-Off Ver
    office365
    Posts
    16

    Re: CountIFS without duplicate

    Quote Originally Posted by AndyLiu View Post
    Hi Watersev,

    thanks again for your help, I just released your formula ignore those duplicated one, how could I include it but consider as one item to add in the sum ?
    Got i! by following:

    =SUMPRODUCT((LEFT(D1:D17,2)="RV")*(A1:A17>=DATE(YEAR($H$1),MONTH($H$1),1))*(A1:A17<=DATE(YEAR($H$1),MONTH($H$1)+1,0))*(1/COUNTIF(D1:D17,D1:D17))*1)

    Please help to advice if there are any alternatives !~ thank you so much

+ 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. Multiple COUNTIFS ignoring duplicate values in a column
    By hamzahs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-27-2015, 11:31 AM
  2. COUNTIFS OR...multiple countifs without duplication in the numbers
    By HooligaD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2012, 09:53 AM
  3. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  4. Issues with using COUNTIFS to highlight duplicate rows
    By Frank Bugeja in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-01-2011, 09:00 PM
  5. Need to duplicate COUNTIFS in Excel 2003
    By TimB in forum Excel General
    Replies: 2
    Last Post: 08-04-2010, 04:49 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1