How to Count Members by Country Who Visited at Least Once in Excel
Question details
The user needs to calculate the total number of members from a specific country (e.g., Ukraine) who visited a cafe on at least one of multiple specified days using Excel formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing attendance or visit data across multiple days to count unique members based on their nationality using dynamic arrays.
- Observed behavior
- Requires a functional dynamic-array formula to filter and count data based on a country lookup and multiple attendance conditions.
Ensure that your data is formatted as Excel Tables (e.g., Table1 for member details and Table2 for attendance) so the structured references in the formula work correctly. Also, verify that your version of Excel or WPS Spreadsheet supports dynamic-array functions like LET and FILTER.
Use LET and FILTER Functions to Count Members
This method combines the LET, FILTER, and VLOOKUP functions to isolate members who visited on specific days and then count those matching the target country.
The LET function allows you to define variables within your formula, making it easier to read and calculate faster. By filtering for members with at least one 'Y' on Monday, Thursday, or Saturday, and then looking up their country, you can accurately count the target demographic.
Click on the empty cell where you want the final count result to be displayed.
Type the following formula into the formula bar: =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))
Press the Enter key to execute the formula. Excel will dynamically calculate and return the total number of members matching your criteria.
Use WPS Spreadsheet to Handle Complex Dynamic Formulas
WPS Spreadsheet fully supports advanced functions like LET, FILTER, and VLOOKUP, allowing you to seamlessly analyze and count condition-based data just like in Microsoft Excel.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your member and attendance data.
- 2. Organize data as tables: Ensure your datasets are properly formatted as Tables so structured references function correctly.
- 3. Input the dynamic formula: Select your target cell and paste the LET and FILTER formula to calculate the targeted visitor count.
- 4. Review results: Press Enter to instantly view your filtered count and analyze the conditional data.
Frequently Asked Questions
What if my Excel version doesn't support the LET function?
If you are using an older version of Excel that lacks support for dynamic arrays like LET or FILTER, you will need to rely on a complex combination of SUMPRODUCT, INDEX, and MATCH, or upgrade to a modern version like Microsoft 365 or WPS Office.
How can I change the formula to count members from a different country?
To count members from another country, simply replace the text "Ukraine" in the VLOOKUP portion of the formula with the name of the new target country, making sure it exactly matches the text in your source table.
Can I add more days to the visit filter condition?
Yes, you can include additional days by appending another addition condition inside the first FILTER function. For example, add +(Table2[Wed]="Y") to also include members who visited on Wednesday.
