Forum Discussion
Counts of an event when a field contains a certain text
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
The idea remains the same:
=SUMPRODUCT((tblActivity[[Mon]:[Sun]]>0)*(VLOOKUP(tblActivity[Member Number], tblMemberDetails, 2, FALSE)="Y"))
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"))
- Thanks! I'll have a go at implementing that.
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!
