Calculation Issues

How to Calculate Weighted Average Cost Basis for Multiple Stocks in WPS Spreadsheets

Huma Ashraf ChHuma Ashraf Ch Nov 13, 2026 871 views

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.

How to Calculate Weighted Average Cost Basis for Multiple Stocks in WPS Spreadsheets
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 you start

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.

Solution 1Recommended

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.

1
Open the transaction workbook

Open WPS Spreadsheets and go to the worksheet that contains the transaction table with columns for stock identifier, shares, and transaction amount.

2
Create the summary cell

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.

3
Enter the SUMIF formula

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.

4
Copy the formula for other stocks

Press Enter and drag the fill handle down for the remaining stock symbols, or copy the formula to the corresponding summary rows.

Calculate Weighted Average Cost Basis with SUMIF in WPS Spreadsheets
Check the totals: The ABS function prevents negative signs from outflow conventions, but commissions and fees must already be included in the transaction amount column or added to the numerator.
WPS Spreadsheets calculation guide

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. 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. 2. Review the transaction table: Open the relevant worksheet and confirm that stock identifier, shares, and amount columns are present and consistently populated.
  3. 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. 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. 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.
Accurate per-stock weighted average cost basis using familiar SUMIF and FILTER formulas.Full compatibility with Microsoft Excel .xlsx workbooks, formulas, and formatting.Free, lightweight spreadsheet tools for investment tracking and cost-basis calculations.Familiar Office-style ribbon and formula engine reduce migration effort.Built-in functions handle multi-stock ledgers without expensive add-ins.
microsoft office alternative - wps office

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.