Search code examples
excelexcel-formulaexcel-2010array-formulasconsolidation

Excel - consolidate with OR operation

I have in a sheet a table like so:

+------+---------+
| Name | Boolean |
+------+---------+
| A    | true    |
| B    | true    |
| B    | false   |
| C    | false   |
| C    | false   |
| A    | false   |
+------+---------+

But in another sheet I need to consolidate it by having a column with unique name and another with the OR of all column Boolean matching the unique name. Like so:

+------+---------+
| Name | Boolean |
+------+---------+
| A    | true    |
| B    | true    |
| C    | false   |
+------+---------+

I tried Consolidate (no OR operation) and also Index/Match combo, and I couldn't make it work with array-formulas. I more or less succeed with an Pivot table, but the result wasn't much satisfactory.

There's a simple way to do this that I can't see?

Solution

  • Maybe try: 

    =COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,TRUE)>0