How to Identify People Over 65 with a Second Bivalent Booster After a Date in WPS Office Spreadsheets
Question details
The user needs a spreadsheet formula to verify whether a person is over 65 and has received at least two qualifying bivalent booster records after April 1, 2023.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with vaccination records and wants to flag rows that meet the combined age and booster-date condition.
- Observed behavior
- The formula should return TRUE for eligible people and FALSE otherwise, including when fewer than two qualifying booster dates exist.
Make sure your date values are real spreadsheet dates and that the vaccine-name range and date range are aligned one-for-one, for example C2:K2 for vaccine names and D2:L2 for dates. In older WPS Spreadsheets versions, be ready to commit array formulas with Ctrl+Shift+Enter.
Use IFERROR, AND, and SMALL in a helper column to flag eligible records
Use this if you need a single TRUE/FALSE result per person and want a formula that also works in older WPS Spreadsheets versions.
This formula checks B2 for age and then uses SMALL to find the second qualifying bivalent booster date from the date range. Because vaccine names are in C2:K2 and the corresponding dates are in D2:L2, the two ranges must stay the same size and aligned.
If there is no second qualifying date, SMALL returns an error; IFERROR converts that error to FALSE so the row is not flagged incorrectly.
Click cell M2, or another empty column in the same row, and type the header "Over65SecondBooster".
In M2 enter: =IFERROR(AND(B2>65,SMALL(IF((C2:K2="Bivalent Moderna")+(C2:K2="Pfizer Bivalent"),D2:L2),2)>DATE(2023,4,1)),FALSE)
If your WPS Spreadsheets version does not spill dynamic arrays automatically, click the formula bar and press Ctrl+Shift+Enter instead of Enter.
Drag the fill handle from M2 down through the remaining data rows to evaluate each person.
TRUE means the person is over 65 and has a second qualifying bivalent booster date after April 1, 2023; FALSE means the condition is not met.
Use FILTER with SMALL when dynamic array functions are available
Use this alternative in WPS Office 365 or dynamic array versions when you prefer a more direct extraction of qualifying dates before testing the second date.
Build the Age and Second Booster Eligibility Formula in WPS Office Spreadsheets
WPS Office Spreadsheets can run the same IF, AND, SMALL, IFERROR, and FILTER formulas you use in Microsoft 365, so you can flag people over 65 with a second qualifying bivalent booster after April 1, 2023 and keep working with .xlsx files.
- 1. Download and launch WPS Office: Download WPS Office from the official WPS website, install it, and open WPS Office Spreadsheets.
- 2. Open your vaccination workbook: Click Menu and Open, then select the .xlsx or other spreadsheet file containing the vaccination records.
- 3. Enter the helper-column formula: Select M2 and enter: =IFERROR(AND(B2>65,SMALL(IF((C2:K2="Bivalent Moderna")+(C2:K2="Pfizer Bivalent"),D2:L2),2)>DATE(2023,4,1)),FALSE)
- 4. Commit and fill down: Press Ctrl+Shift+Enter if needed, then drag the fill handle down to evaluate all rows.
- 5. Total the eligible rows: Use COUNTIF on the helper column, for example =COUNTIF(M2:M100,TRUE), to count people who meet the criteria.
Frequently Asked Questions
Why do I get #NUM! when there is only one qualifying booster date?
SMALL cannot return the second smallest value when fewer than two qualifying dates exist. Wrapping the formula in IFERROR changes that error to FALSE so the row is handled safely.
Do I need Ctrl+Shift+Enter in WPS Office Spreadsheets?
In older WPS Spreadsheets versions that do not support dynamic arrays, yes: click the formula bar and press Ctrl+Shift+Enter. In newer WPS versions that support dynamic arrays, a normal Enter may be enough.
How do I change the cutoff date from April 1, 2023 to another date?
Replace DATE(2023,4,1) with DATE(year,month,day), for example DATE(2024,1,15) for January 15, 2024.
Can I use different booster names or more vaccine columns?
Yes. Update the quoted vaccine names in the IF condition and make sure the vaccine-name range and date range are the same size and aligned one-for-one.
How can I count all people over 65 who meet the second booster condition?
Fill the helper formula down all rows, then use =COUNTIF(M2:M100,TRUE) or =SUMIF(M2:M100,TRUE) on the helper column to total eligible people.
