How to Sum Excel Cells with No Fill Color in WPS Spreadsheets Using VBA or a Helper Column
Question details
WPS Spreadsheets users need to total values in Excel cells that have no fill color because a standard SUMIF formula cannot test cell formatting directly.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is managing a status or payment tracking sheet and needs a reliable sum for uncolored cells, either by adding a helper column or by evaluating fill color with VBA.
- Observed behavior
- A normal SUMIF formula cannot see cell fill color, so totals that depend on highlighting return an incorrect or unavailable result; a helper column or VBA User Defined Function is needed.
Decide whether your worksheet already has a status or paid/unpaid column. If you must test actual fill color, make sure you can save the workbook as a macro-enabled .xlsm file in WPS Spreadsheets.
Use a Helper Column and the SUMIF Formula
Best for maintainable workbooks: mark rows as paid or uncolored in an extra column and let SUMIF total the matching amounts.
This method avoids VBA and is easier to audit. You can enter any consistent marker such as x, 1, or Paid in the helper column, then sum only the rows where the marker is present.
Open the workbook in WPS Spreadsheets, click a column letter next to your amounts, right-click and choose Insert to add a blank column.
Type x, 1, or another consistent marker in the helper column for each row whose amount should be included in the total.
Select the cell where the total should appear, type =SUMIF(paid_range,"x",amount_range), and press Enter. For example, using C2:C20 as markers and B2:B20 as amounts: =SUMIF(C2:C20,"x",B2:B20).
Create a VBA User-Defined Function to Sum Cells with No Fill Color
Use this when fill color itself is the only reliable condition and you cannot add a helper column.
Sum Uncolored Excel Cells in WPS Spreadsheets
Use WPS Spreadsheets to sum cells with no fill color with a helper column and SUMIF, or apply a VBA User Defined Function for color-based totals. WPS opens and saves Excel .xlsx and .xlsm files, so your formulas and macros keep working.
- 1. Download and install WPS Office: Go to the WPS Office download page, install WPS Spreadsheets, and launch the application.
- 2. Open your Excel workbook: Click File > Open, select the .xlsx or .xlsm file, and enable editing if prompted.
- 3. Apply the helper column or VBA solution: Use the SUMIF helper column method for a maintainable total, or open the VBA editor with Alt+F11 and insert the SumUncolored function for color-based summing.
- 4. Save and recalculate: Save the file in a compatible format such as .xlsx or .xlsm, then press F9 after changing fill colors to refresh any VBA result.
Frequently Asked Questions
Can WPS Spreadsheets use the same SUMIF formula as Excel to sum with a helper column?
Yes. Select the result cell, enter =SUMIF(paid_range,"x",amount_range), and press Enter. WPS Spreadsheets uses the same formula syntax as Excel.
Will the VBA function work if I save the workbook as a normal .xlsx file?
No. Macro-enabled VBA code is stored only in .xlsm workbooks. In WPS Spreadsheets, use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).
Why does the total not change when I recolor a cell?
Changing fill color does not always trigger calculation. Press F9 to recalculate, or use Application.Volatile in the VBA function to mark it for recalculation.
Can I sum only colored cells instead of no-fill cells with this function?
Yes. Modify the VBA condition to check a specific Interior.Color or ColorIndex instead of xlColorIndexNone, then use the same function.
