Formula Errors

Update Paired Inventory Quantities Automatically with Excel Formulas

John WilsonJohn Wilson Nov 20, 2026 868 views

Question details

Create formulas that automatically total left and right inventory quantities entered in alternating columns for each date.

Update Paired Inventory Quantities Automatically with Excel Formulas
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the left total formula

Select B3 and enter =SUMPRODUCT((MOD(COLUMN(D3:S3)-COLUMN(D3)+1,2)=1)*(D3:S3)).

2
Enter the right total formula

Select C3 and enter =SUMPRODUCT((MOD(COLUMN(D3:S3)-COLUMN(D3)+1,2)=0)*(D3:S3)).

3
Fill formulas for other dates

Select B3:C3 and drag the fill handle down through the rows for the remaining dates.

4
Enter and check quantities

Enter numeric quantities in alternating pairs across D3:S3. Check that B3 shows the left total and C3 shows the right total.

Total alternating quantity columns with SUMPRODUCT
Adding more pairs: If you add entry columns beyond S, extend D3:S3 in both formulas to include them, then fill the revised formulas down. Keep labels and total cells outside the entry range.
Work with inventory in WPS

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. 1. Open the worksheet: Open WPS Spreadsheets and use Open to select your inventory workbook.
  2. 2. Add the left total: Select B3 and enter =SUMPRODUCT((MOD(COLUMN(D3:S3)-COLUMN(D3)+1,2)=1)*(D3:S3)).
  3. 3. Add the right total: Select C3 and enter =SUMPRODUCT((MOD(COLUMN(D3:S3)-COLUMN(D3)+1,2)=0)*(D3:S3)).
  4. 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.
Automatically total alternating left and right inventory entries.Open and edit Microsoft Excel XLSX and XLS files.Fill formulas down for additional dates.Work with a familiar spreadsheet interface.
microsoft office alternative - wps office

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.