Posts by QAM

    Heya,


    I used to have a similar formula for what I want to accomplish at my previous job, but I cannot for the life of me remember.


    I have a table with columns containing the following type of data


    | January |
    | 1RB |
    | 1RB |
    | 1RB |
    | 1RB |
    | 2RB |
    | 1RB |
    | 1RB |
    | 1PR |
    | 2PR |
    | 1FO |


    I'm looking for a formula that can sum the numerical values based on the two digit suffix. For the data above, the following results would be expect:


    | RB | 8 |
    | PR | 3 |
    | FO | 1 |


    I wish i knew how I did it previously but would really appreciate some help.


    Thanks,


    David

    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 :?

    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??!

    Hey all,


    I've been trying to work on a project for my employer.


    We are a member organisation where each membered account is given an internal ID, however, the same person can have multiple accounts.
    We're trying to instigate a Unique ID which is individual to each separate person, use a reversed Date of Birth : YYYYMMDDXX


    The XX suffix is dependent if the same date of birth recurs within the data set, but with a different user's name.


    I've tried using Countif to try and work this out, but it seems to have an issue if a member has more than 3 different accounts.



    Would anyone be able to help? I don't mind if the odd ID requires manual checking, but at the moment, I've got several hundred to review.


    I've included a sample of my data.



    Cheers,


    David