Sum Deposits by Date, Investment Type, and Account in WPS Spreadsheet
Question details
Create formulas that sum deposit amounts for a selected account and date while matching investment categories contained within descriptive transaction text.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The worksheet has deposit amounts, account names, dates, and transaction descriptions. The user wants separate totals for Dividends, Interest, and Capital Gains.
- Observed behavior
- The required total must include only rows matching the specified account and date where the description contains the selected investment-type text.
Confirm that the amount column contains numeric values and that the date values in both the source table and summary table are real spreadsheet dates. Identify the columns used for account, date, description, and deposit amount before entering the formula.
Use SUMIFS with wildcard text criteria for each investment type
Use this method when the transaction description includes additional words before or after Dividends, Interest, or Capital Gains.
The asterisks in criteria such as "*Dividends*" are wildcards. They allow SUMIFS to find the category anywhere in the transaction-description cell while also applying the account and date conditions.
In WPS Spreadsheet, verify that column A contains account names, column B contains deposit amounts, column C contains dates, and column D contains transaction descriptions. In your summary area, place the target date in C2.
Select the first Dividends total cell and enter =IFERROR(SUMIFS($B$2:$B$100,$A$2:$A$100,"Account A",$C$2:$C$100,$C2,$D$2:$D$100,"*Dividends*"),""). Press Enter.
Copy the formula to the cells for other investment types, then replace "*Dividends*" with "*Interest*" or "*Capital Gains*". Keep the dollar signs in the source ranges so they remain fixed.
Drag the fill handle down to calculate totals for other dates. To calculate another account, replace "Account A" with the required account name or reference a cell containing the account name, such as $A2.
Reference account and category cells for a reusable summary table
Use this method when your summary table has account names in rows and investment categories in headers, so one formula can be filled across and down.
Build Flexible Deposit Summaries with WPS Spreadsheet SUMIFS
WPS Spreadsheet lets you use SUMIFS and wildcard text criteria to total deposits by account, date, and investment type. Create a reusable summary table, fill formulas across categories, and keep your financial data organized in a familiar spreadsheet workspace.
- 1. Open the deposit worksheet: Launch WPS Office and open the workbook in WPS Spreadsheet.
- 2. Create summary headings: Add account, date, and investment-type headings in an empty area of the sheet.
- 3. Enter the SUMIFS formula: Select the first total cell and use SUMIFS with the amount, account, date, and description ranges.
- 4. Add wildcard category matching: Use criteria such as "*"&D$1&"*" to match the category label stored in the summary header.
- 5. Fill the formula across the table: Drag the fill handle across categories and down through the remaining dates or accounts.
Frequently Asked Questions
How do I sum only Dividend deposits when the description has extra text?
Use wildcard criteria in the SUMIFS description condition, such as "*Dividends*". The asterisks match any text before or after the word Dividends.
Why does my SUMIFS formula return a blank result?
If the formula uses IFERROR, a blank can mean that no rows match all criteria or that a referenced range has an issue. Check the account spelling, date values, and description text in the source data.
Can I show zero instead of a blank when there are no matching deposits?
Yes. Use SUMIFS without IFERROR, because SUMIFS returns 0 when no matching numeric records exist. You can also replace the final "" in IFERROR with 0.
How can I use a cell value instead of typing Account A in the formula?
Replace "Account A" with a cell reference. For example, if the account name is in A2, use $A2 as the account criterion in SUMIFS.
Do the dates have to match exactly in SUMIFS?
Yes, the criterion $C2 matches the same underlying date value. If source entries include times, use date-range criteria such as ">="&$C2 and "<"&$C2+1 instead of an exact date match.
