How to Group Excel Counts into 200-Unit Ranges for a Chart in WPS Office
Question details
The user needs to organize institution counts into 200-unit ranges such as 1–200, 201–400, and 401–600, count how many records fall into each range, and create a chart from the grouped counts.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a column of raw institution counts and wants to summarize them into fixed-size numeric ranges for a chart.
- Observed behavior
- Raw counts are not grouped, so they need helper formulas or another aggregation method to turn the values into chart-ready group totals.
Open the workbook that contains the raw institution counts and identify the count column, such as C. Confirm that the count cells are numeric so the CEILING.MATH formula and COUNTIF summary work correctly.
Group Counts with CEILING.MATH and Count Each Range with COUNTIF
Use this formula-driven method when you want a transparent, automatically updating summary that directly feeds a column or bar chart.
This method adds a helper column that converts each raw count into a fixed-width group ID, then counts each group ID to create chart-ready totals.
In an empty helper column, such as D, click D2 and enter ="G"&TEXT(CEILING.MATH(C2/200),"00"). Press Enter, then drag the fill handle down through all count rows.
On a new sheet or in an empty area, type the possible group IDs in column A (for example G00, G01, G02, G03), or enter =UNIQUE(Sheet2!D2:D99999) if your version supports dynamic arrays.
In cell B2, enter =COUNTIF(Sheet2!D:D,A2) and copy it down next to each group ID to calculate how many records fall into each range.
Select the group ID and count cells, go to Insert > Chart, choose Column or Bar, and then adjust the chart title and axis labels as needed.
Use a PivotTable and PivotChart to Group the Count Ranges
Use this alternative when you prefer an interactive summary and want to change the grouping or explore the data without writing COUNTIF formulas.
Group Raw Count Values into Chart-Ready Ranges in WPS Office Spreadsheets
Use WPS Office Spreadsheets to organize raw counts into 200-unit groups with the CEILING.MATH helper formula, count each group with COUNTIF, and insert a column or bar chart in a familiar Excel-compatible environment.
- 1. Open the workbook: Open your workbook in WPS Office Spreadsheets and locate the raw count column.
- 2. Add the helper formula: In the helper column, enter ="G"&TEXT(CEILING.MATH(C2/200),"00") in the first data row and fill it down.
- 3. Count each group: Create a group ID list and use =COUNTIF(Sheet2!D:D,A2) next to each ID to calculate range totals.
- 4. Build the chart: Select the group IDs and counts, go to Insert > Chart, and choose Column or Bar.
Frequently Asked Questions
What does the CEILING.MATH(C2/200) part of the formula do?
It divides the count by 200 and rounds the result up to the nearest whole number. Counts 1–200 become 1, 201–400 become 2, and 401–600 become 3; TEXT then formats that number as G01, G02, or G03.
Why do blank or negative counts become G00?
Blank cells are treated as 0, and negative counts round toward 0, so they produce G00. Filter them out before creating the chart if you only want positive count ranges.
Can I use a different range size instead of 200?
Yes. Change the divisor in the helper formula, for example ="G"&TEXT(CEILING.MATH(C2/500),"00") for 500-unit ranges, and adjust the group labels as needed.
Will the grouped chart update when new count rows are added?
The group counts update if the helper formula is filled down to new rows and the COUNTIF formula references the full column, such as D:D. For dynamic group ID lists, use UNIQUE or manually add new IDs.
Do I need a PivotTable to create this grouped chart?
No. The helper column and COUNTIF method creates chart-ready totals directly. A PivotTable and PivotChart are useful alternatives if you want an interactive summary without COUNTIF formulas.
