How to Use SUMIFS to Return Monthly Profit by Name in WPS Office Spreadsheets
Question details
The user needs to summarize profit by person and month from a source range that contains a name field, transaction date, and profit amount, using a SUMIFS-based formula.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a profit ledger with names, transaction dates, and profit amounts and needs a monthly per-person profit summary without manually filtering the data.
- Observed behavior
- The goal is a reusable formula that returns each person's monthly profit total and can be filled across a summary table.
Open your workbook in WPS Office Spreadsheets and confirm that the source data has separate columns for name, transaction date, and profit amount. If the source range contains merged cells, unmerge them before creating the formula.
Use a Helper Month Column with SUMIFS
Best when you can add a helper column to the source data; it keeps the formula short and makes month labels easy to match.
A helper column converts each transaction date into a month name with TEXT. The summary formula then sums the profit column where both the name and the generated month match the summary cells.
In the source sheet, insert a column next to the transaction date. In the first data row, enter =TEXT(A2,"mmmm") and drag the fill handle down to convert every row into a month name such as January.
Click the cell where the first monthly total should appear, then enter =SUMIFS(Bets!$AB$11:$AB$5000,Bets!$G$11:$G$5000,$A3,Bets!$W$11:$W$5000,B$1). Adjust the sheet name, rows, columns, and criteria cells to match your layout.
Drag the fill handle down the name column and across the month row to apply the formula to every name-month combination.
Use Date Boundaries in SUMIFS without a Helper Column
Use this if you prefer not to add a helper column and want the calculation based directly on the transaction date.
Use SUMIFS in WPS Office Spreadsheets to Summarize Monthly Profit by Name
WPS Office Spreadsheets lets you replicate SUMIFS monthly profit summaries with a helper month column or date boundaries, so you can work with Excel-compatible formulas in a free, familiar spreadsheet app.
- 1. Open WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the workbook containing the profit source data.
- 2. Prepare the source data: Make sure the profit records are in a structured range with no merged cells, and add a helper month column using =TEXT(A2,"mmmm") or set up month boundary cells.
- 3. Enter SUMIFS in the summary sheet: In the first monthly profit result cell, enter =SUMIFS(profit_range,name_range,name_cell,month_or_date_range,criteria_cell) or use date criteria such as ">="&start_date and "<"&next_month_start.
- 4. Fill the summary table: Drag the fill handle down and across so WPS Office Spreadsheets calculates the profit total for every person and month automatically.
Frequently Asked Questions
Can I calculate monthly profit by name without a helper column?
Yes. Use date-bounded SUMIFS criteria with the first day of the month and the first day of the next month, for example =SUMIFS(profit_range,name_range,name_cell,date_range,">="&start_date,date_range,"<"&next_month_start).
Why does my SUMIFS formula return zero or wrong totals?
Check that the month names in the criteria cell exactly match the helper column text, and make sure the source data has no merged cells. Date criteria work only when the date column contains real dates, not text.
How do I create a helper month column in WPS Office Spreadsheets?
Insert a column next to the transaction date, enter =TEXT(A2,"mmmm") in the first row, and drag the fill handle down to fill all records.
Can I use a PivotTable instead of SUMIFS for this summary?
Yes. Select the source data, go to the Insert tab, choose PivotTable, then place Name in the Rows area, Month in the Columns area, and Profit in the Values area after removing merged cells.
Will these SUMIFS formulas work with Microsoft Excel files?
Yes. WPS Office Spreadsheets opens and saves .xlsx workbooks, so the same SUMIFS formulas can be used in Excel-compatible files without conversion.
