Excel Formula to Count Residents by Date and Vaccine Type in WPS Office
Question details
The user needs a spreadsheet formula that counts resident records when at least one of several paired date columns falls within a four-month window and the corresponding vaccine column contains Moderna Bivalent or Pfizer Bivalent.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is managing resident immunization or vaccine tracking records with multiple date columns and their associated vaccine columns.
- Observed behavior
- The goal is to return an accurate row count while keeping each date column paired with its corresponding vaccine column instead of treating all columns independently.
Confirm the exact column letters for the resident marker, vaccine columns, and date columns in your worksheet, and make sure the date columns contain real Excel/WPS dates rather than text so comparison with TODAY() works correctly.
Use SUMPRODUCT to Count Residents by Matching Date and Vaccine Columns
Use this row-by-row formula when each date column must be tested only with its paired vaccine column, which prevents false matches from unrelated columns.
COUNTIFS can evaluate multiple criteria, but it applies each criterion independently across the whole range. In this dataset, that would allow a date in one column to be paired with a vaccine value in another column, producing unreliable results. SUMPRODUCT evaluates each row as one record and preserves the relationship between each date column and its matching vaccine column.
Replace the example ranges G2:G1000, X2:X1000, W2:W1000, and the other letter ranges with your actual data range. The formula counts a resident once when any paired date falls between TODAY()+120 and TODAY()+150 and the matching vaccine cell contains either vaccine name.
Select an empty cell in your WPS Spreadsheets worksheet where you want the resident count to appear.
Type or paste the following formula in the formula bar, then adjust the row ranges to match your data: =SUMPRODUCT((G2:G1000="Resident")*(((X2:X1000>=TODAY()+120)*(X2:X1000<=TODAY()+150)*((W2:W1000="Moderna Bivalent")+(W2:W1000="Pfizer Bivalent")))+((Z2:Z1000>=TODAY()+120)*(Z2:Z1000<=TODAY()+150)*((Y2:Y1000="Moderna Bivalent")+(Y2:Y1000="Pfizer Bivalent")))+((AB2:AB1000>=TODAY()+120)*(AB2:AB1000<=TODAY()+150)*((AA2:AA1000="Moderna Bivalent")+(AA2:AA1000="Pfizer Bivalent")))+((AD2:AD1000>=TODAY()+120)*(AD2:AD1000<=TODAY()+150)*((AC2:AC1000="Moderna Bivalent")+(AC2:AC1000="Pfizer Bivalent")))+((AF2:AF1000>=TODAY()+120)*(AF2:AF1000<=TODAY()+150)*((AE2:AE1000="Moderna Bivalent")+(AE2:AE1000="Pfizer Bivalent")))+((AH2:AH1000>=TODAY()+120)*(AH2:AH1000<=TODAY()+150)*((AG2:AG1000="Moderna Bivalent")+(AG2:AG1000="Pfizer Bivalent")))+((AJ2:AJ1000>=TODAY()+120)*(AJ2:AJ1000<=TODAY()+150)*((AI2:AI1000="Moderna Bivalent")+(AI2:AI1000="Pfizer Bivalent")))>0))
Change "Moderna Bivalent" and "Pfizer Bivalent" to the exact values in your vaccine cells, and change 120 or 150 if you need a different four-month window.
Press Enter to calculate the result. WPS Spreadsheets returns the number of resident rows that match at least one valid date-and-vaccine pair.
Count Resident Records with Date and Vaccine Criteria in WPS Spreadsheets
Use the SUMPRODUCT approach in WPS Spreadsheets to count resident records accurately when date and vaccine columns must stay paired. The formula runs natively in .xlsx files, so you can update your tracking workbook without changing tools.
- 1. Open your workbook: Open the resident-tracking workbook in WPS Spreadsheets by double-clicking the .xlsx file or using File > Open.
- 2. Select the result cell: Click the empty cell where you want the resident count result to appear.
- 3. Enter the SUMPRODUCT formula: Type or paste the SUMPRODUCT formula from the recommended solution into the formula bar and adjust the row ranges to your data.
- 4. Confirm criteria: Check that the resident marker range, date columns, and vaccine names match the exact spelling used in your worksheet.
- 5. Calculate and review: Press Enter to calculate the count, then spot-check a few rows to confirm the result meets your four-month window.
Frequently Asked Questions
Why does COUNTIFS give the wrong count for multiple date and vaccine column pairs?
COUNTIFS applies each criterion independently across the entire range, so a date from one column can be paired with a vaccine value from another column. Use SUMPRODUCT to evaluate each row as a single record and keep each date column paired with its corresponding vaccine column.
Can I use full column references such as G:G and X:X in SUMPRODUCT?
WPS Spreadsheets supports full column references, but they can slow down calculation because every cell in the column is evaluated. It is better to use a bounded range such as G2:G1000 or a named range that covers only the populated cells.
How do I include both Moderna Bivalent and Pfizer Bivalent in the formula?
In each paired vaccine-column test, use addition for the two equality checks: (W2:W1000="Moderna Bivalent")+(W2:W1000="Pfizer Bivalent"). This returns 1 if either vaccine name matches, and the date condition confirms that the matching vaccine is associated with the date in the paired column.
How can I count only resident rows?
Multiply the whole SUMPRODUCT condition by (G2:G1000="Resident") before the paired date and vaccine tests. Rows that are not marked as Resident evaluate to FALSE and are excluded from the final count.
What should I do if my date columns are stored as text?
Text dates may not compare correctly with TODAY()+120 and TODAY()+150. Format the date columns as Date, or convert text dates with DATEVALUE, and make sure there are no leading spaces or inconsistent date formats before recalculating.
