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