I have a data set where there are multiple rows with the same ID#. If both DATA_A and DATA_B are "Y" for each of the same ID#'s I want it to return "1" (Like ID#002 below). If there is one "N" I want it to return "0" for each row with that ID# (Like ID#003 below).
I attempted to do =MAXIFS($F$2:$F$8,$B$2:$B$8,B2,$C$2:$C$8,"Y",$D$2:$D$8,"Y") ,but this would return 1 for ID#003 when I don't want that.
If anyone is able to suggest any other formulas to try or thought processes I'd greatly appreciate it!
| A | B | C | D | E | F |
| 1 | ID | DATA_A | DATA_B | What I want it to return | Ignore this column |
| 2 | 001 | N | Y | 0 | 1 |
| 3 | 002 | Y | Y | 1 | 1 |
| 4 | 002 | Y | Y | 1 | 1 |
| 5 | 003 | N | N | 0 | 1 |
| 6 | 003 | Y | Y | 0 | 1 |
| 7 | 003 | Y | Y | 0 | 1 |
| 8 | 004 | Y | Y | 1 | 1 |
I attempted to do =MAXIFS($F$2:$F$8,$B$2:$B$8,B2,$C$2:$C$8,"Y",$D$2:$D$8,"Y") ,but this would return 1 for ID#003 when I don't want that.
If anyone is able to suggest any other formulas to try or thought processes I'd greatly appreciate it!
