How to Calculate Rolling Monthly Yes Percentages by Region in WPS Spreadsheets
Question details
The user needs a formula to calculate the percentage of Yes responses for each region within a rolling monthly date period.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has data with Region, Date, and Response columns and wants a rolling monthly percentage of Yes answers for each region.
- Observed behavior
- They need to divide the count of Yes responses by the total responses that meet the same region and rolling date conditions.
Check that your Region, Date, and Response data are in separate columns and that the Date column contains real date values rather than text.
Use COUNTIFS with EDATE for a one-month rolling Yes percentage
Use this solution when the rolling period means the calendar month ending on the date in H1.
This solution uses EDATE to create a one-month rolling window ending on the date in H1. The numerator counts only Yes responses, and the denominator counts all responses in the same region and date period.
Open WPS Spreadsheets and select an empty cell, such as I2, where the first regional percentage should appear.
Type =COUNTIFS($E$2:$E$200,H2,$D$2:$D$200,">="&EDATE($H$1,-1),$D$2:$D$200,"<="&$H$1,$F$2:$F$200,"Yes")/COUNTIFS($E$2:$E$200,H2,$D$2:$D$200,">="&EDATE($H$1,-1),$D$2:$D$200,"<="&$H$1) and press Enter.
With the result cell selected, go to the Home tab, open the Number Format drop-down, and choose Percentage.
Use H1-30 for an exact 30-day rolling Yes percentage
Use this variation when the rolling period must be exactly the 30 days ending on the H1 date, instead of the previous calendar month.
Calculate Rolling Regional Yes Percentages with WPS Spreadsheets
Use WPS Spreadsheets to create a COUNTIFS formula that divides the number of Yes responses by all regional responses in a rolling date window, then format the result as a percentage.
- 1. Prepare the data in WPS Spreadsheets: Open your workbook in WPS Office and make sure Region, Date, and Response are in separate columns, with dates recognized as real dates.
- 2. Enter the rolling Yes percentage formula: Click an empty cell and enter the COUNTIFS/COUNTIFS formula using $E$2:$E$200, $D$2:$D$200, $F$2:$F$200, H2 as the region, and H1 as the period end date.
- 3. Format the result as a percentage: Select the formula cell, go to the Home tab, and select Percentage from the Number Format drop-down.
- 4. Copy the formula for other regions: Use the fill handle or copy the formula to adjacent rows, and update H2 for each region while keeping the data ranges absolute.
Frequently Asked Questions
Why does the denominator COUNTIFS not include a Yes condition?
Because it needs to count all responses in the same region and date period, so the Yes count is divided by the total number of responses to get a percentage.
How do I make the rolling period exactly 30 days instead of one calendar month?
Replace EDATE(H1,-1) with H1-30 in both the numerator and denominator. For example, the date condition becomes ">="&H1-30 and "<="&H1.
Why do I see a decimal like 0.75 instead of 75%?
Select the result cell, go to the Home tab in WPS Spreadsheets, and choose Percentage from the Number Format drop-down.
Can WPS Spreadsheets open an Excel workbook that already contains this formula?
Yes, WPS Spreadsheets supports COUNTIFS and EDATE, and it opens and saves .xlsx files with formula compatibility.
What should I check if the formula returns #VALUE! or zero?
Check that the dates are true dates, not text, and that H1 and H2 contain a valid date and region matching the data.
