How to Use SUMIFS to Calculate Stock Option Profit by Ticker in WPS Office Spreadsheets
Question details
The user needs a formula to calculate stock option profit or loss by ticker, matching the symbol in another worksheet and excluding rows whose status is Open.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user tracks option trades across Puts, Calls, and other strategy worksheets and wants to populate a ProfitGrid summary by ticker.
- Observed behavior
- The user expects to sum values from column AF on a strategy sheet where column A matches the ticker and column AK is not "Open".
Make sure every strategy sheet uses ticker symbols in column A, profit or loss values in column AF, and a status in column AK. If a sheet name contains spaces, wrap it in apostrophes when using it in the formula.
Use SUMIFS to sum closed profit or loss by ticker from a strategy sheet
Use this formula for a ticker such as the one in ProfitGrid cell A4 when profit or loss is in column AF and Open positions should be excluded.
This single formula matches the ticker in column A of the strategy sheet and excludes rows where column AK equals Open. It returns the sum of column AF for all matching non-Open rows.
Whole-column references such as $AF:$AF and $AK:$AK include all current and future trade rows, which is useful when the workbook will contain hundreds of trades.
Go to the ProfitGrid worksheet and select cell B4 for the Puts profit or loss result.
Type =SUMIFS(Puts!$AF:$AF,Puts!$A:$A,$A4,Puts!$AK:$AK,"<>Open") and press Enter.
Copy B4 to C4 for Calls or to other strategy columns and ticker rows. Change Puts to the actual sheet name and confirm column AF contains the profit or loss values.
Adapt the formula to track current market value of open positions
Use this variation when you want the Summary sheet to total only rows whose status is Open, such as current open option positions.
Calculate Stock Option Profit by Ticker with WPS Office Spreadsheets
Use WPS Office Spreadsheets to build a ticker-based profit and loss summary with SUMIFS, whole-column references, and Microsoft Excel-compatible formulas without macros or complex add-ins.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu, choose Open, and select the .xlsx workbook from your computer or OneDrive.
- 2. Go to the ProfitGrid worksheet: Click the ProfitGrid sheet tab at the bottom of the workbook and select cell B4.
- 3. Enter the SUMIFS formula: Type =SUMIFS(Puts!$AF:$AF,Puts!$A:$A,$A4,Puts!$AK:$AK,"<>Open") and press Enter.
- 4. Copy the formula to other cells: Drag the fill handle from B4 to C4 or down the ticker rows, changing Puts to Calls or the appropriate strategy sheet name.
- 5. Save as a Microsoft Excel file if needed: Click Menu > Save As and choose .xlsx to keep full compatibility with Microsoft Excel.
Frequently Asked Questions
What does "<>Open" do in the SUMIFS formula?
The "<>" operator means not equal, so "<>Open" excludes every row in column AK that contains exactly Open and includes all other statuses.
Can I copy the formula from Puts B4 to Calls C4 without editing it?
No, you need to change the sheet name from Puts to Calls because SUMIFS does not automatically update sheet names when you copy the formula across columns. The $A4 ticker reference updates by row, but sheet names and range references stay as typed.
Why use whole-column references such as $AF:$AF and $AK:$AK?
Whole-column references make the formula include every current and future trade row automatically. This is useful when the workbook will grow to hundreds of option trades.
How do I track current market value of open positions instead of closed profit?
Use the same SUMIFS structure but change "<>Open" to "Open". For example, in Summary cell Q4 enter =SUMIFS(Puts!$AF:$AF,Puts!$A:$A,$A4,Puts!$AK:$AK,"Open") and adjust the sheet name for each strategy.
Does this SUMIFS formula work in both WPS Office and Microsoft Excel?
Yes, SUMIFS is supported in both applications, and the formula can be used in .xlsx workbooks without changing the syntax.
