Return TRUE Only When All Matching Rows Are TRUE in Excel
Question details
Return the same result for each repeated number in column A, with TRUE only when all corresponding values in column B are TRUE.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains repeated numbers in column A and TRUE or FALSE values in column B.
- Observed behavior
- Every row with the same number should display TRUE if all values in its group are TRUE; otherwise, it should display FALSE.
Check that the group numbers are in column A and that column B contains logical TRUE or FALSE values, not text. Adjust the row limits in the formulas to cover all of your data.
Use COUNTIFS to check each matching group
Use this formula to return TRUE when a group contains no FALSE values.
Select C2 and enter =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,FALSE)=0.
Drag the fill handle from C2 down to the last data row. Rows with the same value in column A will return the same result.

Use FILTER and AND in Microsoft 365
Use FILTER to collect a group's values and AND to test whether they are all TRUE.
Check Matching Rows with COUNTIFS in WPS Spreadsheets
Use COUNTIFS in WPS Spreadsheets to check each group for FALSE values and return the same result for matching rows.
- 1. Open your spreadsheet: Open WPS Spreadsheets and select your workbook using Open.
- 2. Check the data: Confirm that column A contains the group numbers and column B contains logical TRUE or FALSE values.
- 3. Enter COUNTIFS: Select C2 and enter =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,FALSE)=0. Change row 100 to the last row of your data if needed.
- 4. Fill down: Drag the fill handle from C2 to the last data row to calculate the result for every group.

Frequently Asked Questions
Why do all rows with the same number return the same result?
The COUNTIFS formula checks the entire specified range for FALSE values associated with the number in column A. Each matching row therefore evaluates the same group.
What happens if one matching row is FALSE?
COUNTIFS finds at least one FALSE for that number, so the comparison =0 returns FALSE for every row in that group.
How do I include more than 100 rows?
Change both range endpoints in the formula, such as $A$2:$A$100 and $B$2:$B$100, to the same last data row. Then fill the revised formula down column C.
Can I use these formulas if column B contains blanks?
The formulas shown assume column B contains logical TRUE or FALSE values. If blanks must prevent a TRUE result, use =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,TRUE)=COUNTIF($A$2:$A$100,A2) in C2 and fill it down.




