How to Pull Excel Data into Another Table Using Multiple Criteria in WPS Spreadsheets
Question details
The user needs to pull toll transaction data into another table by matching a license plate, a date or time range, and a plaza code, then return or total the matching amount.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a source table of toll transactions and wants to populate a summary table with amounts that match multiple criteria.
- Observed behavior
- The goal is to return or total the amount from rows where the license plate, transaction date/time range, and plaza code all match the criteria cells.
Confirm that the source data uses consistent date/time formats and that the criteria cells for license plate, start date, end date, and plaza code contain the same value types as the source columns.
Use SUMIFS to Total Amounts with License Plate, Date Range, and Plaza Code
Best when the source data is an Excel Table or named ranges and you want a single total based on multiple criteria.
SUMIFS evaluates all conditions together and sums only rows where every criterion is true. It works with structured references and standard cell references in WPS Spreadsheets.
Click the cell where you want the matching total to appear, then type =SUMIFS( to begin the formula.
Enter the sum range first, such as amount, then the first criteria range and criteria cell, for example licencePlate,plate.
Add transactionDate,">="&dateIn,transactionDate,"<="&dateOut to include transactions between the start date and end date cells.
Add PlazaCode,code and press Enter. The completed formula is =SUMIFS(amount,licencePlate,plate,transactionDate,">="&dateIn,transactionDate,"<="&dateOut,PlazaCode,code).
Use SUMPRODUCT for Multiple Criteria with Regular Cell Ranges
Use this alternative when the source data is in fixed cell ranges instead of a structured Excel Table.
Pull Excel Data by Multiple Criteria with WPS Spreadsheets
Use WPS Spreadsheets to apply SUMIFS or SUMPRODUCT formulas that pull toll amounts from a source table by plate, date range, and plaza code—all in a familiar Excel-compatible interface.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the Excel or .xlsx file containing the toll transaction source data and the summary table.
- 2. Click the summary cell: Select the cell where you want the returned or total amount to appear.
- 3. Enter the SUMIFS formula: Type =SUMIFS(amount,licencePlate,plate,transactionDate,">="&dateIn,transactionDate,"<="&dateOut,PlazaCode,code) and press Enter.
- 4. Verify and save: Confirm the total matches the expected records, then save the file as .xlsx to keep Excel compatibility.
Frequently Asked Questions
Why does my SUMIFS date condition return 0 even when dates look correct?
Make sure the transaction date column and the date criteria cells are real dates or date-time values, not text. Use ">="&startDate and "<="&endDate so WPS Spreadsheets compares the cell values as dates.
Can I use SUMIFS across another worksheet?
Yes. Add the sheet name before each range, for example Sheet1!$D$2:$D$17 for the sum range and Sheet1!$B$2:$B$17 for a criteria range.
What is the difference between SUMIFS and SUMPRODUCT for multiple criteria?
SUMIFS is the recommended function for summing numeric data and supports structured Excel Table references. SUMPRODUCT is an alternative that works with regular cell ranges and array-style condition checks.
How do I return individual matching rows instead of only the total amount?
For displaying the matching transaction rows, use WPS AutoFilter with the license plate, date, and plaza columns, or create a PivotTable. SUMIFS and SUMPRODUCT return the summed amount.
