How to Sum and Average Every Other Column by Category in WPS Office Spreadsheets
Question details
The user needs formulas that calculate the sum and average of numeric values in alternating columns, but only for columns whose header row matches a selected category or color.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet stores numeric values in alternating columns, with text labels in the columns between them. The user wants to aggregate only the target numeric columns for a selected category.
- Observed behavior
- Normal SUM and AVERAGE formulas either include all columns or cannot apply the alternating-column and category condition at the same time.
Check that the category labels such as red, blue, and green are in one consistent header row and that the values to calculate sit in the same columns as those labels. In WPS Spreadsheets, select the result cell and make sure the formula bar is ready before pasting the formula.
Use an Array Formula to Sum and Average Every Other Column by Category
Use this method when your values are in alternating columns, headers contain category names or colors, and you need a total or average for only one selected category.
The formula multiplies two conditions: the header equals the selected category and the column number is even. It then sums or averages only the columns where both conditions are true. The same formula works in WPS Spreadsheets and Excel-compatible workbooks.
Open the workbook in WPS Office Spreadsheets and click the cell where you want the category total to appear.
Paste the following formula into the formula bar: =SUM(IF(($A$2:$GS$2="red")*(MOD(COLUMN($A$2:$GS$2),2)=0),$A$3:$GS$3))
Click another result cell and paste: =AVERAGE(IF(($A$2:$GS$2="red")*(MOD(COLUMN($A$2:$GS$2),2)=0),$A$3:$GS$3))
If WPS Spreadsheets does not spill the formula automatically, press Ctrl+Shift+Enter to enter it as an array formula; otherwise press Enter.
If the target numeric columns are odd-numbered, change MOD(...,2)=0 to MOD(...,2)=1. Replace red with the header value that matches your category, such as blue or green.
Use SUMPRODUCT for a Non-Array Method
Use this alternative when you prefer a formula that normally does not require Ctrl+Shift+Enter and you want the same category-based alternating-column calculation.
Calculate Category Totals and Averages in Alternating Columns with WPS Spreadsheets
Open the workbook in WPS Spreadsheets and use Excel-compatible array formulas to sum or average only the alternating columns that match a category. WPS keeps the familiar formula behavior and file format, so you can calculate without rearranging the source data.
- 1. Open the workbook: Launch WPS Office Spreadsheets, click Menu > Open, and select the Excel workbook that contains the alternating value and category columns.
- 2. Select the result cell: Click the cell where you want the category total or average to appear, then click the formula bar to place the cursor.
- 3. Paste the formula: Paste the SUM or AVERAGE array formula from this article and replace red, the header range, and the value range with your own category and data references.
- 4. Enter as an array if needed: Press Enter if WPS Spreadsheets supports dynamic arrays; otherwise press Ctrl+Shift+Enter to confirm the formula as a legacy array formula.
- 5. Check the result: Confirm that the returned value includes only the target alternating columns for the selected category.
Frequently Asked Questions
Why does the formula use MOD(COLUMN(...),2)=0?
It checks the column number and divides by 2. Even-numbered columns produce a remainder of 0, so the formula targets every other column in the range. Change =0 to =1 to target odd-numbered columns instead.
Can I replace red with another category or a cell reference?
Yes. Replace red with another header value such as blue, or use a cell reference such as $B$1 if the category name is stored in a cell. Keep the comparison range the same as the header range.
Do I need Ctrl+Shift+Enter in WPS Spreadsheets?
If your WPS Spreadsheets version supports dynamic arrays, pressing Enter is enough. In versions that require legacy array formulas, select the cell and press Ctrl+Shift+Enter.
What should I change if my data starts at a different row or column?
Update the header range and the value range so they cover the same columns. For example, change $A$2:$GS$2 and $A$3:$GS$3 to match your actual row numbers and column span, keeping the ranges aligned.
How do I use the average formula for odd columns instead of even columns?
Use the AVERAGE(IF(...)) version, change MOD(COLUMN(range),2)=0 to MOD(COLUMN(range),2)=1, and adjust the category text if necessary.
