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