I’ve been working on updating some reporting I do for a client.
We use Citrix reports to generate a list of logged in users by CPU time, I export that to CSV then start sorting.
The data is organized into 6 columns, the first column is the username, the second is the CPU time they used, this is all I get from Citrix. I then add a third column which uses an IF statement to note if they used CPU time, the fourth column uses a MATCH statement to isolate users with a particular prefix in their username, the fifth column is the same as the fourth, but looks for a different prefix, the sixth column uses an IF statement to count how many users were logged in and have either of the two specific prefixes I am looking for in columns four and five.
Here is what the formulas look like:
=IF(B2>0,1,0)
[checks to see if user had CPU time]
=IF(ISNA(MATCH(“domainprefix*”,A2,0)),0,1)
[checks to see if the user has either prefix I'm looking for, the * is a wildcard, so it will return true when there is a prefix match]
=IF((AND(C2=1,((OR(D2=1,E2=1))))),1,0)
[checks to make sure user was logged in and a has either prefix]
If I’m feeling extra creative I might figure out how to combine the prefix checks into the last IF statement.
Sources: Personal-Computer-Tutor, PC Mag