Forum Discussion
Think this requires the IF function?
I have a table (table1) of the list of members with attributes in cells.
| member id | car | country |
| 1 | ford | united states |
| 2 | chrysler | ukraine |
| 3 | rolls royce | ukraine |
| 4 | porche | ukraine |
I also have another table (table2) with the list of members and when they visit a cafe which is only open for 3 days a week.
| member id | Mon | Thu | Sat |
| 1 | Y | Y | N |
| 2 | Y | Y | Y |
| 3 | N | N | N |
| 4 | N | Y | N |
Question is "how many Ukrainians have visited the cafe at least once this week? (Answer is 2)
What's the best way to do this?
=LET(id, FILTER(Table2[member id], (Table2[Mon]="Y")+(Table2[Thu]="Y")+(Table2[Sat]="Y")), f, FILTER(id, VLOOKUP(id, Table1, 3, FALSE)="ukraine", ""), COUNTA(f))
HansVogelaar thank you once again for such a quick response!
The example tables are somewhat simplified for my real spreadsheet and I'm having difficulty implementing the solution you have kindly given in my spreadsheet.
Could the formula be simplified if table2 contained a column flagging whether a member id had been at the cafe during the week, meaning we wouldn't need to include Mon, Thu, Sat cells in the equation?
So column active is Y if Y for any of the days or N if N for all of the days.
member id active Mon Thu Sat 1
Y Y Y Y 2 Y Y Y Y 3 N N N N 4 Y N Y N Thanks!
If the two tables are guaranteed to have the same IDs in the same order:
=COUNTIFS(Table1[country], "ukraine", Table2[Active], "Y")
