Update Paired Inventory Quantities Automatically with Excel Formulas
Question details
Create formulas that automatically total left and right inventory quantities entered in alternating columns for each date.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Inventory values are entered in consecutive left-and-right column pairs, with a separate row for each date.
- Observed behavior
- The user wants separate left and right totals to recalculate as paired quantities are entered.
Identify the first left and right entry columns and choose two total cells outside the entry range. The example below uses D3:S3 for paired entries and B3:C3 for totals; adjust the references to match your worksheet.
Total alternating quantity columns with SUMPRODUCT
Use separate formulas to add the left and right values in each row and update the totals when entries change.
In this example, D is the first left column, E is the first right column, and the pattern continues through S. These formulas add the entered values; they do not calculate a running balance by subtracting stock movements.
Select B3 and enter =SUMPRODUCT((MOD(COLUMN(D3:S3)-COLUMN(D3)+1,2)=1)*(D3:S3)).
Select C3 and enter =SUMPRODUCT((MOD(COLUMN(D3:S3)-COLUMN(D3)+1,2)=0)*(D3:S3)).
Select B3:C3 and drag the fill handle down through the rows for the remaining dates.
Enter numeric quantities in alternating pairs across D3:S3. Check that B3 shows the left total and C3 shows the right total.

Calculate Paired Inventory Totals in WPS Spreadsheets
Use SUMPRODUCT in WPS Spreadsheets to total alternating left and right quantity columns and update each date's totals as you enter values.
- 1. Open the worksheet: Open WPS Spreadsheets and use Open to select your inventory workbook.
- 2. Add the left total: Select B3 and enter =SUMPRODUCT((MOD(COLUMN(D3:S3)-COLUMN(D3)+1,2)=1)*(D3:S3)).
- 3. Add the right total: Select C3 and enter =SUMPRODUCT((MOD(COLUMN(D3:S3)-COLUMN(D3)+1,2)=0)*(D3:S3)).
- 4. Fill and test: Drag the fill handle from B3:C3 down to other date rows, then enter quantities in D3:S3 and check the totals.

Frequently Asked Questions
Why are the left and right totals in separate formulas?
The MOD test identifies alternating positions in the entry range. The formula using =1 totals the first, third, and other odd positions; the formula using =0 totals the even positions.
Do I need to insert columns after B and C?
Not necessarily. Place the two formulas in any available cells outside the paired entry range, and adjust D3:S3 and COLUMN(D3) to match the first and last entry columns.
How do I include more quantity pairs?
Extend D3:S3 in both formulas to the last entry column, while keeping COLUMN(D3) as the first entry column. Then fill the updated formulas down for the other dates.
Why does the SUMPRODUCT formula return an error?
Check that the entry range contains numeric quantities or blank cells rather than text or error values. Also make sure the formula cell is outside the range it totals to avoid a circular reference.
Will these formulas calculate inventory remaining after stock is used?
No. They add the left and right entries separately. A remaining-balance calculation needs a defined starting quantity and a rule for which entries to add or subtract.




