EXCEL QUESTION LOL

March 5, 2012 8:05 AM

I have a data set of user activity. It records every time an activity is completed by a user, with the date and user ID attatched. I need to tally up the number of times, by user, the user completes the activity on a unique day.

So, I have the following data (basically) where the columns A=UserID, B=1 if activity was initiated and completed, 2 if activity was initiated and not completed, and C=Date

A B C
User1 1 Jan1
User1 0 Jan1
User1 1 Jan1
User2 1 Jan1
User3 1 Jan1
User1 1 Jan2
User2 1 Jan2


I want to tally up all the times where column B=1, but only have it tallied once if the corresponding date in column C is duplicated. So the results would look like this:

User1: 2
User2: 2
User3:1


Please help me make this formula. I've been fiddling around with =COUNT and =COUNTIF but I've exhausted my abilities, and I have not been able to google this

posted by Patbon to Technology (13 answers total)

Best answer: Pivot tables. PIVOT TABLES. This is a 30 second activity in Pivot tables. Create your pivot table, and make your rows User and Date, and your values count of user. Done.
posted by brainmouse at 8:23 AM on March 5, 2012 [2 favorites]

Best answer: Oh, I misread what you were trying to calculate in my excitement about pivot tables so you have to do it slightly different: Row is User, Values is count of user, Report Filter is whatever column B is labeled, and restrict it to where that's 1. Then done.
posted by brainmouse at 8:26 AM on March 5, 2012

Best answer: (ack, and put date in there too. Still, pivot tables will make this 400 times easier).
posted by brainmouse at 8:27 AM on March 5, 2012

Response by poster: I'm sorry, so is it:

Row: User

Values: Count of user (I have no idea what this means)

Report Filter: Column B and Date
posted by Patbon at 8:59 AM on March 5, 2012

Response by poster: Figured out Count of User, but I'm still fuzzy on how to use Date to get only the ones with a unique date per user
posted by Patbon at 9:02 AM on March 5, 2012

"count of user" means count how many times the user appears. You should add date as another row label, not as a report filter, and now that I actually think I understand what you want: don't use count of user, change the Value Field Settings to min or max of user -- once you've set the report filter to only show you 1's (right?), that will give you a 1 for every date that has at least one 1 in it. Then you can use subtotals to get totals for each user.
posted by brainmouse at 9:13 AM on March 5, 2012

Response by poster: Hmm OK

If I halve values as min or max of user, with the rows as dates, it returns 0s for all dates (report filter is only for the 1s)

(thanks for your help)
posted by Patbon at 9:27 AM on March 5, 2012

is "min or max" a thing? i meant either set it to min, or set it to max. Play around with it and see what you can figure out, pivot tables is an essential skill. Maybe it would work better with date instead of user. You have user & date in the rows, right?
posted by brainmouse at 9:30 AM on March 5, 2012

Response by poster: Yes, and I see where I would use the subtotals. I was doing either min or max, not both.

I'll keep playing around, I can see how close I am.
posted by Patbon at 9:32 AM on March 5, 2012

I wonder if min doesn't work even with the report filter on... It's possible that that's true (I haven't used min a lot). If that doesn't work, I might set up another column to the right of the pivot table that just is =if(isnumber(B1)=true,1,"") (or the right cell number is -- don't just click on the cell though because that does weird things in pivot tables) to get your 1 per date that has activity.
posted by brainmouse at 9:36 AM on March 5, 2012

Response by poster: Yes, that was my initial solution, but then I lose the subtotal usefulness. I'd just do a bunch of =SUMs, but there's thousands of users
posted by Patbon at 9:41 AM on March 5, 2012

then maybe filter one step earlier? Make a new sheet that only has the rows where column B is 1 (by filtering, then copy and pasting), and then do the pivot table with the min values.
posted by brainmouse at 9:47 AM on March 5, 2012

Response by poster: Yes, I'm an idiot. Thanks
posted by Patbon at 9:48 AM on March 5, 2012