Counts of an event when a field contains a certain text

[chrishall166]

Hi, on one tab (Sheet1) I have a table of individual member names and whether they have children (Y/N).

Name	Children
Billy	N
Diane	Y

On another tab (Sheet2) I have a table of individual member names and whether they are present at a meeting on certain days.

Name	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Billy	Y	Y	Y	N	N	N	N
Diane	Y	N	N	Y	N	N	N

How can I display a count of attendances of members who have children with the results in a third tab Sheet3)?

For example Billy is present on Monday, Tuesday and Wednesday but he has no children whereas Diane is present on Monday and Thursday and she has children. So the count of attendances of members who have children would be 2.

Thanks

[#2]

@chrishall166 

For example:

=SUMPRODUCT((Sheet2!B2:H3="Y")*(VLOOKUP(Sheet2!A2:A3, Sheet1!A2:B3, 2, FALSE)="Y"))

If the table on Sheet1 is named Persons and the table on Sheet2 is named Attendance, you can use

=SUMPRODUCT((Attendance[[Mon]:[Sat]]="Y")*(VLOOKUP(Attendance[Name], Persons, 2, FALSE)="Y"))

[#3]

Thanks! I'll have a go at implementing that.

[#4]

Hi, struggling to get the above to work, think I may have simplified the example too much!

This is more representative.

We have a table with details of members as below, tblMemberDetails

Member Number	Children	Disabled
1	Y	N
2	N	N
3	Y	Y

The next table shows the number of eggs the member has eaten whenever they visit. If the cell is empty then the assumption is that they have not visited, tblActivity

Member Number	Mon	Tue	Wed	Thu	Fri	Sat	Sun
1	5	6	5				5
2	2	5			3
3			1			5

What would be the formula for calculating the number of visits by members with children? In the example the answer is 6 but what formula could I use?

Thanks!

[#5 · Accepted solution]

@chrishall166 

The idea remains the same:

=SUMPRODUCT((tblActivity[[Mon]:[Sun]]>0)*(VLOOKUP(tblActivity[Member Number], tblMemberDetails, 2, FALSE)="Y"))