Return TRUE or FALSE When a Store and Product Combination Exists in WPS Office Excel
Question details
The user needs a formula-based check that returns TRUE when a specific Store Number and Product Number appear in the same row, and FALSE otherwise.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is working with a large Excel data set that contains Store Number and Product Number columns and needs to verify whether a given combination exists in the same row.
- Observed behavior
- The desired result is TRUE when both values exist in the same row and FALSE when they do not.
Open the workbook in WPS Office Excel and identify the exact columns containing Store Number and Product Number. If the dataset has hundreds of thousands of rows, plan to use bounded ranges instead of entire-column references.
Use COUNTIFS to check a store-product combination in the same row
Best for large data sets because COUNTIFS evaluates both criteria row by row and directly returns TRUE or FALSE.
COUNTIFS checks whether the Store Number in one column and the Product Number in another column appear in the same row. Bounded ranges such as A2:A100000 are recommended for performance.
Click the first cell where you want the TRUE or FALSE result to appear.
In the formula bar, type =COUNTIFS(A2:A100000,A2,B2:B100000,B2)>0 and replace A2:A100000 and B2:B100000 with your actual store and product ranges.
Press Enter, then drag the fill handle down the result column to check each store-product combination.
Use SUMPRODUCT as an alternative logical test
Use this method if you prefer array-style logic or need an alternative to COUNTIFS with the same TRUE or FALSE result.
Check Store and Product Combinations in WPS Office Excel
Use the COUNTIFS formula in WPS Office Excel to test whether a store number and product number appear in the same row, then copy the formula down to return TRUE or FALSE for every combination.
- 1. Open your workbook: Launch WPS Office Excel and open the workbook that contains the Store Number and Product Number columns.
- 2. Identify the data ranges: Note the Store Number column range, such as A2:A100000, and the Product Number column range, such as B2:B100000.
- 3. Enter the COUNTIFS formula: Click the first result cell and type =COUNTIFS(A2:A100000,A2,B2:B100000,B2)>0 in the formula bar.
- 4. Copy the formula down: Press Enter, then drag the fill handle down the result column to check every store-product combination.
- 5. Read the TRUE or FALSE result: TRUE means the combination exists in the same row; FALSE means it does not.
Frequently Asked Questions
Does this formula work if the Store Number and Product Number are on different sheets?
Yes. You can reference another sheet in COUNTIFS by including the sheet name in the range, for example =COUNTIFS(Sheet2!A2:A100000,A2,Sheet2!B2:B100000,B2)>0.
Why should I avoid entire-column references in COUNTIFS for large data sets?
Entire-column references such as A:A can force Excel to evaluate over a million rows per formula, which may slow down calculation. Use bounded ranges like A2:A100000 to improve performance.
Can I use this formula in both WPS Office and Microsoft Excel?
Yes. COUNTIFS and SUMPRODUCT formulas are supported in WPS Office Excel and Microsoft Excel, and .xlsx workbooks can be opened and edited in both applications.
What does the >0 do in the COUNTIFS formula?
COUNTIFS returns the number of rows that meet both criteria. The >0 comparison converts that count into TRUE when one or more matching rows exist and FALSE when no matching row exists.
