CODEWITHSUNDEEP
excelexcel-2013
Cecil
Cecil

Reputation: 31

How can I count only unique occurrences of a value from table using multiple criteria?

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

Upvotes: 3

Views: 130

Answers (1)

barry houdini
barry houdini

Reputation: 46401

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

Upvotes: 2

Related Questions