Count of unique name and date of birth matches

    Hiya,


    I have a spreadsheet of member details; names, contact details, date of birth.


    The sheet will have multiple entries for the same person; and obviously, some dates of birth with will multiples.


    I want to be able to count the of dates of birth for unique names. Example shows the count i would be expecting in column D.


    | A | B | C | D |
    | David | Jones | 22011986 | 01 |
    | David | Jones | 22011986 | 01 |
    | Paul | Harrison | 08091979 | 01 |
    | Simon | Edgar | 10071980| 01 |
    | Sam | Smith | 08091979 | 02 |
    | Anthony | Jeffs | 22011986 | 02 |


    I've racked by brains on an answer!


    Any help??!

    Re: Count of unique name and date of birth matches


    Should the first 2 rows result in 2 as there are 2 different names with same birthday, just like the last row shows 2? I.e. David Jones and Anthony Jeffs,

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Count of unique name and date of birth matches


    If I am correct and since I have the formula for my suggestiong waiting on my workbook .. I will post it here anyway. Hopefully it works for you....


    Try:


    [COLOR="#0000FF"]=SUMPRODUCT(($C$2:$C$7=C2)/COUNTIFS($A$2:$A$7,$A$2:$A$7&"",$B$2:$B$7,$B$2:$B$7&"",$C$2:$C$7,$C$2:$C$7&""))[/COLOR]


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Count of unique name and date of birth matches


    Hiya,


    There can be multiple entries for the same person, hence rows 1 and 2 being the same, but as one unique birthday for that person.

    Re: Count of unique name and date of birth matches


    Quote from NBVC;774942

    =SUMPRODUCT(($C$2:$C$7=C2)/COUNTIFS($A$2:$A$7,$A$2:$A$7&"",$B$2:$B$7,$B$2:$B$7&"",$C$2:$C$7,$C$2:$C$7&""))


    copied down


    Thanks for the formula -- all it seemed to do was crash Excel unfortunately :?

    Re: Count of unique name and date of birth matches


    Quote from QAM;774952

    Hiya,


    There can be multiple entries for the same person, hence rows 1 and 2 being the same, but as one unique birthday for that person.


    Then why does the last row for Anthony Jeffs have a result of 2?


    Also, how large is your range? My formula is not meant for very large ranges.... you may need to go with a helper column to get the results more efficiently.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Count of unique name and date of birth matches


    All sorted now!!!


    I created a duplicate table and removed duplicates then counted the DOBs.


    Cheers

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login