Counting the number of User Logins per Each Month

[Simbiose]

Hello there,
I can't seem to find or even know what to search for to find a solution to my problem, so I went with creating this thread.
If it's a duplicate, I'm sorry about this and you can point me to a thread that already has the solution and I'll get rid of this one.

I'm trying to find the number of logged users on a ranged date by month, here's a simple example of a table:

So, I need a formula that will calculate the number of login attempts, for instance, of user A for each month and then for user B and each month and so on.
These results would need to be displayed on a 3rd column along the table according the user and month.

Is this even doable without using VBA?

Thanks in advance.

P.S. I forgot to mention that I'm using Office 365, which is equivalent for Excel 2016 if not mistaken.

 

[RobP]

Hi,

welcome.. so as some simple formulas, you could use something like this :

Book1
	D	E	F
1	A	B	C
2	5	2	1
3	2
4	1
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($A$2:$A$21,"1",$B$2:$B$21,"B")
F2	F2	=COUNTIFS($A$2:$A$21,"1",$B$2:$B$21,"C")
D2	D2	=COUNTIFS($A$2:$A$21,"1",$B$2:$B$21,"A")
D3	D3	=COUNTIFS($A$2:$A$21,"2",$B$2:$B$21,"A")
D4	D4	=COUNTIFS($A$2:$A$21,"3",$B$2:$B$21,"A")

As you have to display (and select 2 criteria each time, its a little unwieldly, but as long as you know the size of your range, and the criteria, you can pretty much copy the formula down.

(and BTW, O365 .. is a whole load more exciting than Xl2016 .. lots of new additions, rather than equivalent....)
cheers
Rob

 

[etaf]

quite a few ways to do that

are the months - just numbers 1 , 2 , 3 etc or based on a date
anyway
somehting like this

Book10
	A	B	C	D	E	F	G	H	I	J
1	month			User	1	2	3	4	5	6
2	1	a		a	2	1	1	0	0	0
3	1	b		b	1	0	1	0	0	0
4	1	c		c	1	1	0	1	0	0
5	2	a		f	0	1	0	0	0	0
6	2	c		g	0	0	1	0	0	0
7	2	f		e	0	0	0	1	0	0
8	3	g		t	0	0	0	1	0	0
9	3	a
10	3	b
11	4	c
12	4	e
13	4	t
14	1	a
15	1	b
16	2	c
17	2	f
18	3	g
19	3	a
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=UNIQUE(B2:B13)
E2	E2	=COUNTIFS($A$2:$A$100,E$1,$B$2:$B$100,$D2)
F2:J2,E3:J8	F2	=COUNTIFS($A$2:$A$13,F$1,$B$2:$B$13,$D2)
Dynamic array formulas.

 

[Simbiose]

Thank you both for the time dedicated to investigate and answer my question.
This will surely help.
As both answers work, I've decided to mark etaf's post as the answer as it's a bit more detailed.

 

[etaf]

you are welcome

 

[RobP]

Hi, as mentioned earlier - there are many ways. I spent some time on this way, to give just 1 column of output. How does that look for you ?

Book1
	A	B	C	D
1	month	User		Results
2	1	a		1a = 2
3	1	b		1b = 2
4	1	e		1e = 1
5	2	a		2a = 1
6	2	c		2c = 2
7	2	f		2f = 2
8	3	g		3g = 2
9	3	a		3a = 2
10	3	b		3b = 1
11	4	c		4c = 1
12	4	e		4e = 1
13	4	t		4t = 1
14	1	a
15	1	b
16	2	c
17	2	f
18	3	g
19	3	a
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=LET(data,BYROW(A2:B19,LAMBDA(a,TEXTJOIN("",TRUE,a))),uniqval,UNIQUE(data),results,BYROW(uniqval,LAMBDA(row,SUM(--(row=data)))),UNIQUE(data)&" = "&results)
Dynamic array formulas.