0

I'm trying to work out the maximum number of times a user has accessed the system in a day. There are different dates that they have accessed the system but on some days they have accessed the system more than once and I want to find the day with the highest number.

There are 4 columns: 1. Name (A) 2. Date (B) 3. Total Daily Accesses (C) 4. Highest number of times accessed in a day for each user (D)

I've copied in the formula below which isn't displaying the highest number of times accessed in a day:

=MAX(C:C,A)

If anyone could advise where I am going wrong, I'd really appreciate it.

Thanks

3829219884
  • 57
  • 7
  • Do you need to know the user as well or just the total amount of times accessed? – Roan Aug 09 '17 at 11:34
  • Hi Roan, I need to know the highest for each user – 3829219884 Aug 09 '17 at 11:35
  • How do you get the user to run the formula against? – Roan Aug 09 '17 at 11:51
  • The ```=MAX(IF(A:A="user_1",C:C))``` but after you type in the formula you have to press control+Shift+Enter else it gives a data type error. I found it here: https://stackoverflow.com/questions/20665799/return-max-index-value-in-vlookup – Roan Aug 09 '17 at 11:54

2 Answers2

1

1) Generate a list of unique names using this formula modified to suit your ranges:

=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

The above formula taken from this question

2) Assuming you generated the list of names in column F and lets assume the range F1:F10

=Aggregate(16,6,(A:A=F1)*C:C,1)

Copy that down as far as your list of unique names goes.

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
0

Let's assume the names are in column A, the date in column B; one line per user and date, multiple users, possibly multiple dates. For the formulas, let's assume data in rows 2 to 22.

We need an auxiliary column to hold the combination of name and date, like in column E:
=A2&B2
for cell E2.
To list the total number of daily logins for each user, put in column C
=COUNTIF($e$e:$E$22,$A2&$B2)
Copy this down for each row holding data.

To get the maximum daily events per user, create the list of users in column F (either manually, or using the answer cited in another answer here). Into each line of the list of users, enter
=MAX(IF(A2=A$2:A$22;C$2:C$22))
as an array formula (close with Ctrl+Shift+Enter).

user1016274
  • 4,071
  • 1
  • 23
  • 19