# Think this requires the IF function?

Copper Contributor

# 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?

4 Replies

# Re: Think this requires the IF function?

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

# Re: Think this requires the IF function?

@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 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!

# Re: Think this requires the IF function?

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

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

# Re: Think this requires the IF function?

@Hans Vogelaar Thank you, perfect!