• 408K Members
• 7,547 Online
• 465K Conversations
SOLVED

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
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.

# Re: Count if help

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies