Count of families whose members have gone to dentist. Straightforward?

**RogerRangeRover** · 12-06-2012, 07:45 AM

Hi!

I have a seemingly simple problem at my hands shown in the attached workbook, but can´t seem to get around it. Could you? Thank you!

I have a sample list of adult members of families and child members of families, and would need know how to count the number of families from which any member has visited a dentist (in a certain period, but for simplicity´s sake let´s limit it to having visited at all).

Bests,

Roger

**Fotis1991** · 12-06-2012, 08:01 AM

Maybe

For Edvinson family

=COUNTIFS($H$5:$H$22,B5,$J$5:$J$22,1)

**RogerRangeRover** · 12-06-2012, 08:16 AM

Thank you very much Fotis!

My problem is that in the actual list that I have there are several thousand adults and children. I would not like to use filter, as I need to build in a formula which automatically counts this.

**Fotis1991** · 12-06-2012, 08:22 AM

You are welcome.

But i am not able to understand why you need AutoFilter? My suggested formula does what you want to do.

Can you explain your goal a little more?

**benishiryo** · 12-06-2012, 08:24 AM

hi RogerRangeRover. not sure if i understand you correctly. the 3 families you are referring to are:
Edvison
Gereth
Oconnelly

right? and the list on the right are for the children? is it possible the Durhams do not have any adults visiting the dentist, but have children who does? if so, you probably need to combine the list. here's a formula just for the adults:
=COUNT(INDEX(1/((D5:D11>0)*MATCH(B5:B11,B5:B11,0)=(ROW(B5:B11)-ROW(B5)+1)),,))

**RogerRangeRover** · 12-06-2012, 09:22 AM

Dear Benishiryo,

You are right, that is part of the riddle, there are families, where only children have visited. Thank you for the formula!!! This goes a long way!

I have two lists, one for children and one for adults, which are both in the thousands. There are around 30 columns for each row with different types of data, one being the visit to dentist. The lists are continously updated with new people, new data on the 30 items. Still there need to be two separate lists for book-keeping. Would it be possible to have a table which automatically updates when new families, adults and chidlren are entered on the two lists? Thanks alot!