Think this requires the IF function?

Copper Contributor

I have a table (table1) of the list of members with attributes in cells.

 

member idcarcountry
1fordunited states
2chryslerukraine
3rolls royceukraine
4porcheukraine

 

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 idMonThuSat
1YYN
2YYY
3NNN
4NYN

 

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?

4 Replies

@chrishall166 

 

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

@Hans Vogelaar 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 idactiveMonThuSat

1

YYYY
2YYYY
3NNNN
4YNYN

 

Thanks!

@chrishall166 

If the two tables are guaranteed to have the same IDs in the same order:

 

=COUNTIFS(Table1[country], "ukraine", Table2[Active], "Y")

@Hans Vogelaar Thank you, perfect!