3

I am trying to calculate the percent of users for each role (admin, mod, general user, etc.) who have logged in over a specified timeframe (1 month, 3 months, 6 months, 1 year). Editing the data table that I'm pulling from is not an option as this spreadsheet is supposed to be self-calculating (if that makes sense). Right now I only need the numbers because calculating the % should be simple.

The columns that I am using are id, eventTime, ScreenName, and userGroup.

For example, I want to count how many users from the userGroup "admin" logged in within the last month. Using ScreenName because it's unique and evenTime because ya.. The data I am pulling from records the time, username, user group, and the page name for every page the user looked at on the website so a user may have several entries on the same day.

The code I have written so far:

=SUMPRODUCT(((data!$B:$B>(TODAY()-30))*(data!$B:$B<=TODAY()))*(data!$G:$G=G$1))

'data' is the sheet that I'm trying to count from.

Here is some pseudo-code to help:

SUMPRODUCT((eventTime>30 days ago)*(evenTime<todays date))*(userGroup = referenced userGroup))

If I were to log in as an Admin and than proceed to look at seven pages on the website, the data would have seven different rows storing the page/time/myusername/myrole data. The equation above is counting each individual instance that my username pops up instead of how many users have logged in. e.g. I want it to return "1" because only one user logged in that month, even though they looked at several pages. But the equation is returning "7".

TL:DR I'm trying to list the number of active users within a specified timeframe. My formula was for one month. But all I need is for it to count only unique screenName/every screen name once.

Example of what the data looks like that I'm pulling from

Cœur
  • 37,241
  • 25
  • 195
  • 267
Cecil
  • 31
  • 3

1 Answers1

2

If you have a list of all the ScreenNames (listed once only, named ScreenNamesList) then the simplest way to do this is like this, assuming ScreenNames are in column A:

=SUMPRODUCT((COUNTIFS(data!$B:$B,">"&TODAY()-30,data!$B:$B,"<="&TODAY(),data!$G:$G,G$1,data!$A:$A,ScreenNamesList)>0)+0)

....but if you don't have such a list then you can use this formula - note ranges reduced, whole columns will be very slow:

=SUM(IF(FREQUENCY(IF(data!$B$2:$B$1000>TODAY()-30,IF(data!$B$2:$B$1000<=TODAY(),IF(data!$G$2:$G$1000=G$1,MATCH(data!$A$2:$A$1000,data!$A$2:$A$1000,0)))),ROW(data!$A$2:$A$1000)-ROW(data!$A$2)+1),1))

formula needs to be confirmed with CTRL+SHIFT+ENTER

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • thanks for the answer Barry! I meant to add an image last night, so I added an example of what the data looks like if it helps. – Cecil Jun 05 '15 at 13:24
  • When you say list? are you just referring to a simple column filled with the unique screen names? I was able to make some VBA that filtered out duplicates and pasted it to column A on a new sheet called 'screen_names". – Cecil Jun 05 '15 at 13:57
  • Can I use a pivot table as the list? – Cecil Jun 05 '15 at 14:09
  • Also is in this part: data!$A:$A,ScreenNamesList)>0)+0). is the last "data!" referring to the page location of the ScreenNamesList? – Cecil Jun 05 '15 at 14:55
  • Yes the list can be a row or column on the worksheet - if you then name it you can use the list as I suggested or you can just use the cell reference - don't know if pivot table will work. My formula assumes that column A on data sheet is where the ScreenNames are located - I see from your example that it's column.C, so obviously you need to change the formula accordingly..... – barry houdini Jun 05 '15 at 17:20
  • I'm not sure why but I can't get the formula to return anything but zero. I created a VBA button that will generate a column of unique screen names on sheet "screen_names" and then I clicked column A (where its located on the new sheet) and named that column ScreenNamesList. Now in the formula all I should've had to change was the $A:$A to $C:$C, I think. Do you know where I am still making my mistake? Thanks you've been a lot of help – Cecil Jun 08 '15 at 14:10
  • Ok - sorry for multiple posts in a row but I can't edit my last posts. I was able to get it to work :) thanks. I ended up just highlighting the range of the unique screen names. But is there a way to get it to be dynamic? I will be using this spreadsheet with other data models and there is a chance that a future set of data might have more usernames - and I want to ensure that the formula reads all the usernames instead of stopping at the number previously used. thanks. – Cecil Jun 08 '15 at 14:29
  • One possibility is to use a dynamic named range - see here: http://blog.contextures.com/archives/2010/04/26/dynamic-excel-named-list-grows-automatically/ – barry houdini Jun 08 '15 at 14:44