How to Use an Excel Formula to Check Residency, Age, and Bivalent Vaccinations in WPS Office
Question details
The user needs a worksheet formula that returns TRUE when a person is a resident, is older than 65, and has received two qualifying bivalent booster vaccinations.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is checking a spreadsheet of people and needs to flag records that meet three conditions: resident status, age over 65, and two qualifying bivalent booster vaccinations.
- Observed behavior
- The worksheet should evaluate the age, residency, and vaccination columns and mark each row that satisfies all three conditions.
Before entering the formula, confirm your worksheet has a clear age column, a residency column, and a vaccination range that contains the two bivalent booster entries you want to count.
Use an AND COUNTIF Formula to Check Residency, Age, and Bivalent Booster Status
Use this solution when you need to mark each row that meets all three requirements in WPS Spreadsheets or Excel.
The formula combines AND with COUNTIF. COUNTIF counts how many cells in the vaccination range match Moderna or Pfizer Bivalent Booster, SUM adds those counts, and AND requires all three conditions to be true.
Click cell H2, or the first blank cell in the column where you want the TRUE/FALSE result to appear.
Type =AND(E2>65,G2="Resident",SUM(COUNTIF(W2:AB2,{"Moderna","Pfizer Bivalent Booster"}))=2) and adjust E2, G2, and W2:AB2 to match your actual age, residency, and vaccination ranges.
Press Enter to confirm the formula, then drag the fill handle in the lower-right corner of the cell down through the remaining rows.
If the first shot should not be counted, replace W2:AB2 with Y2:AB2 or your booster-only range before filling down.
Check Vaccination Eligibility in WPS Spreadsheets
Use WPS Office Spreadsheets to run the same Excel AND COUNTIF formula, flag eligible residents over 65, and keep your workbook in .xlsx format without losing compatibility.
- 1. Download and install WPS Office: Visit the WPS Office download page and install WPS Office on your Windows, macOS, Android, iOS, or Linux device.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the workbook that contains the residency, age, and vaccination columns.
- 3. Enter the recommended formula: Select the first result cell and enter the AND COUNTIF formula from the solution above, adjusting cell references as needed.
- 4. Fill down and save: Use the fill handle to apply the formula to all rows, then save the workbook as .xlsx to keep Microsoft Excel compatibility.
Frequently Asked Questions
What does the AND COUNTIF formula actually check?
It checks that the age cell is greater than 65, the residency cell equals Resident, and the SUM of COUNTIF results for Moderna and Pfizer Bivalent Booster in the vaccination range equals 2.
How do I exclude the first dose from the vaccination count?
Change the vaccination range from W2:AB2 to Y2:AB2, or select only the cells that contain bivalent booster entries, before filling the formula down.
Can I use this formula in both WPS Spreadsheets and Microsoft Excel?
Yes. WPS Spreadsheets supports the AND, COUNTIF, and SUM functions and saves .xlsx files, so the same formula works across both applications.
Why does my formula return FALSE even though the person looks eligible?
Check that the age cell contains a number greater than 65, the residency cell is exactly Resident, and the vaccination range contains exactly two qualifying text entries.
How do I apply the formula to many rows quickly?
Select the cell with the formula, double-click the fill handle in the lower-right corner, or drag it down to fill the formula through the rows that contain data.
