Function Problems

How to Compare Part IDs, Customer IDs, and Prices Across Excel Workbooks

Elise Williams Oct 1, 2026 868 views

Question details

The user needs to compare pricing records across two workbooks where one file contains a combined part and customer ID, while the other file separates them into two columns.

How to Compare Part IDs, Customer IDs, and Prices Across Excel Workbooks

Product: Excel
Device & OS: not provided
Scenario: Reconciling pricing and inventory data across multiple workbooks with mismatched ID formats to identify discrepancies.
Observed behavior: The user wants a formula-based method to match separated ID columns with a combined ID column to extract and compare corresponding price totals.

Before you start

Ensure both Excel workbooks are open and verify the exact delimiter (such as a hyphen or underscore) used to join the IDs in your first file.

Solution 1Recommended

Use SUMIF with Concatenated Criteria

You can use the SUMIF function combined with the ampersand (&) operator to join separate ID columns dynamically and compare them against a combined ID column.

The ampersand (&) allows you to merge the contents of two cells and a text delimiter on the fly. By nesting this inside a SUMIF formula, Excel evaluates the separated IDs as a single string, matching the format of your target workbook without needing helper columns.

Prepare the target cell

In the workbook with separated IDs, select the cell where you want the compared price total to appear (for example, cell H2).

Enter the SUMIF formula

Type the formula =SUMIF($A$2:$A$11,E2&"-"&F2,$C$2:$C$11). In this formula, $A$2:$A$11 is the combined ID range in file 1, E2 and F2 are the separated part and customer IDs in file 2, and $C$2:$C$11 is the price range to sum from file 1.

Apply the formula to the column

Press Enter to calculate the first result. Then, click and drag the fill handle at the bottom-right corner of cell H2 to copy the formula down the entire column.

Identify missing records

Compare the retrieved sums against the expected totals in your current file. A result of 0 typically indicates a missing record or a mismatch in the ID formatting.

Using Absolute References: Using dollar signs ($) in your lookup and sum ranges (like $A$2:$A$11) ensures the lookup area remains locked and does not shift when you drag the formula down to other rows.

Efficient Data Comparison in WPS Spreadsheet

Compare and Reconcile Complex Data Seamlessly with WPS Office

WPS Spreadsheet provides robust formula support, including SUMIF, VLOOKUP, and text concatenation, making it easy to compare combined data across multiple workbooks. It offers a smooth, lightweight experience for data analysis and reconciliation.

1. Open Your Workbooks in WPS Spreadsheet: Launch WPS Office and open both the file containing combined IDs and the file with separate ID columns.
2. Input the Comparison Formula: In your target column, enter the =SUMIF() formula using the & operator to combine the separate ID cell references just as you would in standard spreadsheet software.
3. Drag to Fill and Analyze: Use the fill handle to apply the formula down your column, allowing WPS Spreadsheet to instantly calculate the totals so you can easily spot discrepancies.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx files.Supports advanced data comparison and cross-referencing across multiple open workbooks.Lightweight software that processes large datasets quickly without lag.Free and user-friendly interface that requires no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

How do I compare two columns if the delimiter is different?

If the combined ID uses a different delimiter, such as an underscore instead of a hyphen, simply update the text string in the formula. For example, use E2&"_"&F2 instead of E2&"-"&F2.

Why is my SUMIF formula returning zero even when records exist?

This usually happens if the concatenated text does not exactly match the format of the combined ID in your first file. Check for hidden trailing spaces in your cells or ensure you are using the correct delimiter.

Can I use VLOOKUP instead of SUMIF for this comparison?

Yes. If you only need to return a single matching price rather than summing multiple occurrences, you can use a lookup formula like =VLOOKUP(E2&"-"&F2, $A$2:$C$11, 3, FALSE).

How can I easily highlight the missing records after applying the formula?

You can add a simple subtraction formula in the adjacent column (e.g., =H2-G2) to find the difference, or apply Conditional Formatting to highlight any cell where the SUMIF result does not equal your expected total.

Excel & Data Problems