Combine Broker Data from Two Excel Sheets into a Summary in WPS Office
Question details
The user needs to create a Summary sheet that consolidates broker names entered on two weekly-changing worksheets, removes duplicate broker names, and calculates totals for Billing Class N, Billing Class R, and Binders.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains two worksheets with manually entered broker names that change each week. The user wants to build a Summary sheet that gathers unique brokers and counts Billing Class N, Billing Class R, and Binders without maintaining a fixed broker list.
- Observed behavior
- The finished Summary sheet must merge duplicate names from both source sheets and return accurate totals for Billing Class N, Billing Class R, and Binders.
Confirm both source worksheets use the same column layout and consistent broker spelling, and check your WPS Office version for UNIQUE and VSTACK support if you plan to use a dynamic broker list.
Create a Unique Broker List and COUNTIFS Formulas
Use this method when you need a stable Summary sheet that works in all WPS Office versions, or when VSTACK and UNIQUE are not available.
This approach uses Data > Remove Duplicates to build the criteria list and COUNTIFS to total values from both source sheets. It works everywhere, but the unique broker list must be refreshed after weekly broker changes.
Open the workbook in WPS Office, click the New Sheet button, name it Summary, and enter Broker, Billing Class N, Billing Class R, and Binders in row 1.
Copy broker names from Sheet1 and Sheet2 into the Summary broker column, select the pasted range, go to the Data tab, and click Remove Duplicates to keep only unique broker names.
In cell B2, enter =COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,"N")+COUNTIFS(Sheet2!$A:$A,$A2,Sheet2!$B:$B,"N"), then fill down. In cell C2, replace "N" with "R" to count Billing Class R.
In cell D2, enter a COUNTIFS formula that references the broker criteria in A2 and the Binder column from both sheets, using the marker value your team uses, such as "Yes" or "1". Fill down.
After team members change broker names, repeat the Remove Duplicates step or copy and de-duplicate the source lists again so the Summary totals stay current.
Automatically Merge Brokers with VSTACK, UNIQUE, and COUNTIFS
Use this dynamic array method when your WPS Office version supports UNIQUE and VSTACK so the broker list updates without manual refreshing.
Consolidate Weekly Broker Data with WPS Spreadsheets
Use WPS Office to open your existing workbook, build a Summary sheet, merge broker names from two sheets, remove duplicates, and calculate Billing Class N, R, and Binders with COUNTIFS formulas.
- 1. Open the existing Excel workbook: Launch WPS Office, open the workbook containing Sheet1 and Sheet2, and click the New Sheet button to add a Summary sheet.
- 2. Prepare the Summary columns: Enter Broker, Billing Class N, Billing Class R, and Binders in row 1 and format the header row as needed.
- 3. Merge the broker names: Build a unique broker list by pasting both source columns and using Data > Remove Duplicates, or enter a dynamic UNIQUE/VSTACK formula if supported.
- 4. Add COUNTIFS totals: Enter COUNTIFS formulas that reference Sheet1 and Sheet2 and the broker criteria to calculate N, R, and Binder totals.
- 5. Save and share: Save the workbook as .xlsx in WPS Office so team members can continue updating source sheets and viewing the Summary results.
Frequently Asked Questions
How do I make the summary broker list update automatically when new brokers are added?
Use =UNIQUE(VSTACK(Sheet1!$A$2:$A$1000,Sheet2!$A$2:$A$1000)) in the Summary sheet if your WPS Office version supports dynamic arrays. Otherwise, copy both broker columns and use Data > Remove Duplicates after each weekly update.
Why does my COUNTIFS formula return 0 for a broker that exists?
Check that the broker name is spelled identically in both sheets and in the Summary criteria list; also confirm the Billing Class column uses exactly the same text, because values such as "N " or "n" are not equal to "N".
Can I count Binders if there is no Binder column with a numeric value?
Yes. Adjust the COUNTIFS formula to use the column that records Binders as the criteria range and use the marker your team enters, such as "Yes", "1", or a non-empty value.
Does WPS Office support VSTACK and UNIQUE?
Recent WPS Office versions support many dynamic array functions, including VSTACK and UNIQUE. If you see a #NAME? error, your installed version does not have them; use the Remove Duplicates method instead.
How does the summary avoid double-counting brokers that appear in both sheets?
The unique broker list or UNIQUE formula creates one criteria row per broker. The COUNTIFS formulas then add counts from Sheet1 and Sheet2 for that broker, so totals are combined rather than duplicated.
