SOLVED

# Counts of an event when a field contains a certain text

Copper Contributor

# 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

4 Replies

# Re: Counts of an event when a field contains a certain text

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

# Re: Counts of an event when a field contains a certain text

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

# Re: Counts of an event when a field contains a certain text

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!

best response confirmed by chrishall166 (Copper Contributor)
Solution

# Re: 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"))

1 best response

Accepted Solutions
best response confirmed by chrishall166 (Copper Contributor)
Solution

# Re: 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"))