Forum Discussion
Count if help
My company had a system outage where everyone lost access to different roles and then gained them back. I need to make sure everyone had all the appropriate roles restored, no more or less. In th...
We can add the labels "Deleted" and "Added" in Cells E1 and F1, respectively. In Cell E2, we enter the formula:
=IF(OR(ISBLANK(C2),
COUNTIFS($A$2:$A$19,$A2,$D$2:$D$19,C2)),"",
C2), copied down to Cell E19. In Cell F2, we enter the formula:=IF(OR(ISBLANK(D2),
COUNTIFS($A$2:$A$19,$A2,$C$2:$C$19,D2)),"",
D2), copied down to Cell F19.The formulas in Columns E and F return the letter of the Deleted and Added profiles, respectively.
Please see attached file.
Or you may apply conditional formatting rule with approximately the same formula
=NOT((SUMPRODUCT(($A$2:$A$19=$A2)*($C$2:$C$19=$C2)*($C$2:$C$19<>""))=SUMPRODUCT(($A$2:$A$19=$A2)*($D$2:$D$19=$C2)*($D$2:$D$19<>"")))* (SUMPRODUCT(($A$2:$A$19=$A2)*($C$2:$C$19=$D2)*($C$2:$C$19<>""))=SUMPRODUCT(($A$2:$A$19=$A2)*($D$2:$D$19=$D2)*($D$2:$D$19<>""))))