If you had this data set:
Task A, User ID: 34565, Action Taken, User A, 9/18/15
Task A, User ID: 34565, Action Successful, User B, 9/18/2015
Task A, User ID: 45682, Action Taken, User A, 9/17/2015
Task A, User ID: 56789, Action Successful, User B, 9/16/2015
What formula(s) would you use to count how many times User B had an “Action Successful” on “User ID: 34565” on the date 9/18/15 where User A also had an “Action Taken” on the same User ID on the same date?
Hi, welcome to the forum
for future reference, please upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
It sounds like you need to use COUNTIFS() for this. Something like...
=countifs(D:D,"User B",C:C,"Action Successful",B:B,“User ID: 34565”,E:E,"9/18/15")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
=MIN(COUNTIFS(B1:B4,UserID,C1:C4,"Action Successful",D1:D4,"User A",E1:E4,date_reference),COUNTIFS(B1:B4,UserID,C1:C4,"Action Successful",D1:D4,"User B",E1:E4,date_reference))
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
@ daffodil, why the MIN()? What am I missing?
Thank you so much FDibbins and daffodil11. I will be sure to use that guidance in the future. I am wondering if I could tack on another layer of complexity. I think I misspoke, I am actually looking to count how many times one user has that "action taken" status and another user has that "action successful" status on the same user id. The data set is large and I won't be able to plug in that user id everytime. I guess for that section of the formula, it would need to comb column B for duplicates.
Looking at the sample data, I'm seeing
Formula:
Please Login or Register to view this content.
But that relies on one big assumption, that 'Action Successful' will always be directly below 'Action Taken'
Because there may be more on one case than the other e.g. more "Taken" than "Successful"? so take the lowest as matches.
Thank John. I knew I couldn't be all nuts on that.
I assumed the mininum where both were true.
If User A has 5 instances and User B has 3 instances, then I think they both matched a total of 3 times since there would need to be a one-to-one relationship.
Cabbie: If your data is complex, than I'd recommend attaching a desensitized version of your data. Otherwise we'll just be blindly throwing ideas at you.
Last edited by daffodil11; 09-18-2015 at 01:32 PM.
Based on the simple logic that John and daffodil have applied, it seems reasonable to assume that an action can't be successful if it hasn't been taken, so you should only need to count the successful actions.
Looking from a different angle, if 'User B Action Successful' could be tied to 'Action Taken' by 'User A' or 'User C' then you could get miscounts from this method.
daffodill11 great point! Thank you! Will attach shortly. Thank you!
Excel Help Desensitized Data.xlsx here it is!
Here's my first stab at it.
Thanks daffodil11. I soooo apprecaite you taking the time!/My only concern here is that there are varying IDs here unique to each task. It is possible for user B to have a success logged independent of a productivity being logged. For example, if user b takes action a task ID on 8/31/15, but sees success for that ID on 9/1/15, we would not want to count that. Does that make sense? I think the final formula would need to look at the values in row C and count only if those values were the same in each of the countif statements you've written.
Essentially, I am looking to count these instances if and only if the "task productivity" and "task status" cells match the same idea. See example below:
Task Task Productivity 11003138 robotautomation 08/31/2015
Task Task Success 11003138 User E 08/31/2015
What does your ideal output look like?
daffodil11 my ideal output would be exactly what you created. The only addition would be making sure that we only count if the ID is the same. Does that make sense? So, if we say User B was successful... we would want to know if User B was successful where user A was productive. The key that binds these two is the unique ID.
Hi there, Team! Is it kosher to add this conundrum to another thread?
Sorry that took a while, but things got complicated.
This array formula did it for me:
Where N7 was a date, H8 was the was the starting name, J8 is the ending name.
Please Login or Register to view this content.
Holy double post, Batman.
daffodil11 tell me about it! Thank you for giving my issue so much thought! Great that it worked for you! Can you share with me for which date you tested the array formula on and the output?
daffodil11 it looks like it isn't working for me this time around.
Here's an example all filled out.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks