How to Calculate Weighted Average Cost Basis for Multiple Stocks in WPS Spreadsheets
Question details
The user needs to calculate the weighted average cost basis per stock in a spreadsheet by dividing each stock's total transaction cost by its total shares purchased, while avoiding errors from inconsistent ranges, fees, sales, or incorrect stock identifiers.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- An investor maintains a multi-stock transaction ledger containing purchases, fees, sales, dividends, or reinvestments and needs the average cost basis for each ticker on a summary sheet.
- Observed behavior
- The expected result is a per-stock weighted average cost that reflects total cost divided by total shares for the selected stock; incorrect outputs occur when ranges, fees, sales, or identifiers are handled inconsistently.
Before writing formulas, arrange all transactions in one table with consistent columns for stock identifier, shares, and transaction amount. Confirm whether the transaction amount already includes commissions or fees and use the same sign convention for shares and costs.
Calculate Weighted Average Cost Basis with SUMIF in WPS Spreadsheets
Use this when you have a purchase-only or aggregated transaction table and need a formula that works in WPS Spreadsheets and Excel without requiring dynamic arrays.
Weighted average cost assigns the average cost per share by dividing the total cost allocated to a stock by the total shares purchased for that same stock. Keeping the stock identifier, shares, and amount ranges aligned is essential to avoid mixed-stock errors.
Open WPS Spreadsheets and go to the worksheet that contains the transaction table with columns for stock identifier, shares, and transaction amount.
On a separate summary sheet or dashboard, list each unique stock symbol in column A, then select the cell where the weighted average cost should appear.
Type =ABS(SUMIF($A$5:$A$11,A17,$E$5:$E$11))/SUMIF($A$5:$A$11,A17,$B$5:$B$11) and adjust $A$5:$A$11 to your stock identifier column, $E$5:$E$11 to your transaction amount column, and $B$5:$B$11 to your shares column.
Press Enter and drag the fill handle down for the remaining stock symbols, or copy the formula to the corresponding summary rows.

Use the FILTER Formula in Microsoft 365 or WPS Dynamic Array Versions
Use this when your WPS Office or Excel build supports dynamic arrays and you prefer the FILTER-based formula approach.
Extend the Cost-Basis Calculation for Sales, Dividends, and Reinvestments
Use this when the ledger includes taxable transactions beyond simple purchases and you need a more complete cost-basis model.
Calculate Weighted Average Cost Basis in WPS Spreadsheets
Use WPS Spreadsheets to build a cost-basis summary with SUMIF or FILTER formulas, keeping stock identifiers, shares, and transaction amounts aligned for accurate per-stock averages.
- 1. Download and open WPS Office: Go to the WPS Office download page, install WPS Office, and open your existing Excel workbook in WPS Spreadsheets.
- 2. Review the transaction table: Open the relevant worksheet and confirm that stock identifier, shares, and amount columns are present and consistently populated.
- 3. Create the summary formula: In a summary cell, enter =ABS(SUMIF($A$5:$A$11,A17,$E$5:$E$11))/SUMIF($A$5:$A$11,A17,$B$5:$B$11) and adjust the ranges to your data.
- 4. Copy and validate results: Fill the formula down for each stock, then check a few tickers manually to confirm the weighted average matches total cost divided by total shares.
- 5. Save and share as XLSX: Save the workbook in WPS Spreadsheets or export it to Excel .xlsx format when sharing with other Excel users.

Frequently Asked Questions
Can I use the same weighted average cost formula when I have sales in the same table?
For a purchase-only weighted average, yes. If your table includes sales, separate purchase cost and shares from sale proceeds; otherwise sales will reduce the numerator and denominator incorrectly. Use a transaction type column or separate summary columns.
Why does my formula return a negative or #VALUE! error?
A negative result often means the amount column uses outflow signs inconsistently; wrap the numerator in ABS. A #VALUE! error usually means a text value or mismatched range size—check that the stock identifier, shares, and amount ranges cover the same rows.
Do I need Excel 365 or FILTER for this calculation in WPS Spreadsheets?
No. The SUMIF version works in WPS Spreadsheets and Excel without dynamic arrays. Use FILTER only if your version supports it; otherwise use the recommended SUMIF formula.
How do I include commissions and fees in weighted average cost basis?
Add commissions or fees to the transaction amount for each purchase before dividing by shares. If the fee is in a separate column, create a helper column for total cost or add a SUMIF for the fee range to the numerator.
What is the difference between weighted average cost basis and FIFO or specific identification?
Weighted average pools all purchases and divides total adjusted cost by total shares for each stock. FIFO and specific identification track tax lots individually and can produce different realized gains, especially in taxable accounts.




