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"))
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"))