For the same Project_Id if the first cell (in ‘Success’) is 1, than make all ‘output’ cells for that matching project_id a 1….
And for the same Project_Id if the first cell (in ‘Success’) is 0, than make all ‘output’ cells for that matching project_id a 0….
I had previous used:
“IF((COUNTIFS($A:$1:A1,A1,$B$1:B1)),0,1)”
However this is not correct as it does the following: if there are ANY 0’s in the range, than make it 0.
Would appreciate some help!!
| Project_Id | Success | output |
| column A | column B | column C |
|——————–|—————————–|————————-|
| 1 | 1 | 1 |
| 1 | 0 | 1 |
| 1 | 0 | 1 |
| 1 | 0 | 0 |
| 1 | 1 | 0 |
| 1 | 1 | 0 |
| 1 | 1 | 0 |
| 1 | 1 | 0 |
| 1 | 1 | 0 |
>Solution :
To make the output column match the value of the first cell in the Success column for each Project_Id, you can use the following formula in cell C2 and then fill it down for the rest of the column:
=IF(MIN(IF($A$2:$A$9=A2,$B$2:$B$9))=1,1,0)
Explanation:
The MIN function is used to find the minimum value in the range $B$2:$B$9 that corresponds to the same Project_Id as the current row (i.e., the minimum value of Success for that Project_Id).
The IF function is used to check whether the minimum value of Success for the current Project_Id is 1. If it is, the output is 1; otherwise, the output is 0.
I hope this helps! Let me know if you have any questions.