SOLVED

Highlighted
Deleted
Not applicable

# 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 the A column are everyone's usernames.

In the B column are the actions per user, either "Profile deleted" or "Profile added".

In the C column are which profile was added/deleted

I need to make sure that for each user, the exact profiles that were deleted matches the exact profiles that were added. I've attached a sample file

Any help? Thanks

2 Replies
Highlighted
Best Response
Solution

# Re: Count if help

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.

```=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<>"")))*