How to Combine Part Data from Two Excel Sheets and Calculate Averages in WPS Office
Question details
The user needs to combine part numbers from two yearly worksheets, retrieve their descriptions, total quantities, and calculate an average only for part numbers present in both years.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains part data for two years on separate worksheets or ranges; the user needs one consolidated summary without averaging parts that are missing from one year.
- Observed behavior
- The annual data is currently split across separate ranges, so a combined list and conditional average calculation are required.
Before you start, place each year's part numbers, descriptions, and quantities in adjacent columns and make sure the part numbers in both sheets use the same format. Create a summary sheet so the combined list and calculations are separate from the yearly source data.
Use XLOOKUP, SUMIF, and LET to Combine Two Years of Part Data
This is the recommended method when the two yearly ranges have different sizes and part numbers can appear more than once. It creates a dynamic summary that shows a dash when a part is missing from either year.
The sample ranges below match the original layout: first-year part numbers are in E2:E13, descriptions in F2:F13, and quantities in G2:G13; second-year part numbers are in I2:I7, descriptions in J2:J7, and quantities in K2:K7. Replace these ranges with your actual cells and add sheet names if the data is on separate worksheets.
Launch WPS Office, open the workbook that contains your two years of data, and click a blank sheet where you will build the combined list.
In the summary sheet, enter Part Number, Description, and Average in A1:C1. Starting in A2, type or paste every part number from both years that you want to include.
Click cell B2 and enter =XLOOKUP(A2,E$2:E$13,F$2:F$13,XLOOKUP(A2,I$2:I$7,J$2:J$7,"?",0),0). Press Enter. If the data is on another sheet, add the sheet prefix such as Sheet1! before the ranges.
Click cell C2 and enter =LET(year1,SUMIF(E$2:E$13,A2,G$2:G$13),year2,SUMIF(I$2:I$7,A2,K$2:K$7),IF(AND(year1>0,year2>0),(year1+year2)/2,"-")). Press Enter to return the average only when both years have data.
Select B2:C2, drag the fill handle down through the last part number row, and WPS Spreadsheets will apply the XLOOKUP and conditional average formulas to the entire list.
Consolidate Two Years of Part Data and Return Conditional Averages in WPS Spreadsheets
Use WPS Spreadsheets to combine part numbers from two worksheets with XLOOKUP, SUMIF, and LET. The formulas update automatically, return a dash when a part is missing from either year, and work with familiar Excel file formats.
- 1. Download and install WPS Office: Get WPS Office from the official WPS site, install it, and open the workbook that has your two yearly part data sheets.
- 2. Open WPS Spreadsheets: Launch WPS Spreadsheets, go to the File menu, and open the Excel workbook containing the yearly worksheets you want to consolidate.
- 3. Enter the formulas in a summary sheet: Create a new sheet, list the part numbers in column A, and enter the XLOOKUP and LET/SUMIF formulas shown above in columns B and C.
- 4. Review and save in Excel format: Drag the formulas down through the list, verify the averages and dash results, then save the file as .xlsx to share with Microsoft Excel users.
Frequently Asked Questions
What do I do if a part number appears multiple times in one year?
Keep the same SUMIF ranges. SUMIF adds all matching quantity rows, so repeated part numbers are totaled correctly without manual merging.
How can I use these formulas when the two years are on separate worksheets?
In WPS Spreadsheets, start the formula with =, click the other sheet tab, and select the range. WPS inserts the sheet name automatically, such as Sheet2!$I$2:$I$7. You can also type the sheet name followed by !.
Why does the average cell show a dash instead of a number?
The IF(AND(year1>0,year2>0)...) condition returns "-" when either yearly total is zero or missing, so the average is calculated only when both years contain data for that part.
Can I save this workbook and open it in Microsoft Excel?
Yes. WPS Spreadsheets supports .xlsx and other Excel formats. Use File > Save As and choose Excel Workbook (*.xlsx) to share the file.
