Calculation MAX of variable reach

[Zazu_0420]

Hey

I am currently trying to calculate how many streams we had each month.

In the file we have some data extracted from Twitch. I column P it tells the number of minutes we have streamed. Those are the columns I wish to show in a chart later on.
Therefor I want to give each stream we did a number in column B. So basically a column B will show if the row is the first or the X' stream of the month.

Each 2 weeks we will download the last 14 days insights from Twitter and add them to the Excel file.

I tried something with a formula* I found on a forum, but that formula gives all the max number of the above cells
Is it possible to add another variable that would take column A (Month) in account?

So the formula would calculate that if P<>0 it would give me the max number (B) of that month (A) and add 1 to that.

Hopefully it is kind of clear what I am trying to do here.

Thanks in advance



* =MAX(INDIRECT(IF(AND(COLUMN()>0,COLUMN()<257),IF(COLUMN()>26,CHAR(64+INT((COLUMN()-1)/26)),"")&CHAR(65+MOD(COLUMN()-1,26)),"")&"1:"&IF(AND(COLUMN()>0,COLUMN()<257),IF(COLUMN()>26,CHAR(64+INT((COLUMN()-1)/26)),"")&CHAR(65+MOD(COLUMN()-1,26)),"")&ROW()-1))

 

[alansidman]

We cannot manipulate data in a picture. Please upload a sample of your data using XL2BB. It would also be beneficial if you mocked up a solution based upon your sample. If you need instructions on XL2BB, see my signature block.

 

[Zazu_0420]

Problem file.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Maand	Stream X vd maand	Datum	Reclames (minuten)	Reclametijd (seconden) per uur	Gem. aantal kijkers	Chatberichten	Chatters	Clipweergaven	Clips gemaakt	Volgers erbij	Kijkers van hosts en raids (%)	Live weergaven	Maximaal aantal kijkers	Minuten bekeken	Minuten gestreamd
4	7	#VERW!	1/07/2020	0	0	0	30	2	12	0	11	0	0	0	0	0
5	7	#VERW!	2/07/2020	0	0	2927	755	39	2	0	25	0	540	39	7054	239
6	7	#VERW!	3/07/2020	0	0	0	2	2	0	0	3	0	0	0	0	0
7	7		4/07/2020	0	0	0	0	0	0	0	1	0	0	0	0	0
8	7	#VERW!	5/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
9	7	#VERW!	6/07/2020	0	0	0	0	0	0	0	1	0	0	0	0	0
10	7	#VERW!	7/07/2020	0	0	0	0	0	0	1	0	0	0	0	0	0
11	7	#VERW!	8/07/2020	0	0	12922	90	27	0	1	33	0	3726	216	16540	128
12	7	#VERW!	9/07/2020	0	0	8256	564	27	2	1	22	0	3431	168	16759	203
13	7	#VERW!	10/07/2020	0	0	16738	92	31	0	1	31	0	4954	235	21425	128
14	7		11/07/2020	0	0	0	0	0	1	0	2	0	1	0	0	0
15	7		12/07/2020	0	0	0	0	0	0	1	4	0	0	0	0	0
16	7		13/07/2020	0	0	0	0	0	0	0	4	0	0	0	0	0
17	7		14/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
18	7	#VERW!	15/07/2020	0	0	14354	451	43	0	3	27	0	4902	230	21961	153
19	7	#VERW!	16/07/2020	0	0	4995	355	30	0	2	12	1	1744	79	7243	145
20	7		17/07/2020	0	0	0	0	0	0	0	2	0	0	0	0	0
21	7		18/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
22	7		19/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
23	7		20/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
24	7		21/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
25	7	#VERW!	22/07/2020	0	0	196	533	46	0	1	21	2	7403	288	31752	162
26	7		23/07/2020	0	0	0	0	0	1	0	0	0	9	0	0	0
27	7		24/07/2020	0	0	0	0	0	10	2	0	0	0	0	0	0
28	7		25/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
29	7		26/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
30	7		27/07/2020	0	0	0	0	0	0	1	1	0	0	0	0	0
31	7		28/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
32	7	#VERW!	29/07/2020	0	0	10755	602	64	0	2	40	0	3638	165	14627	142
33	7	#VERW!	30/07/2020	0	0	6557	667	30	1	1	16	0	3102	101	15671	239
34	7		31/07/2020	0	0	0	0	0	1	0	0	0	4	0	0	0
35	8	1	1/08/2020													2
36	8	#VERW!	2/08/2020													3
Blad1

Cell Formulas
Range	Formula
B4:B6,B8:B10	B4	=IF(AND([@[Minuten gestreamd]]<>0,Maand=[@Maand]),MAX(INDIRECT(IF(AND(COLUMN()>0,COLUMN()<257),IF(COLUMN()>26,CHAR(64+INT((COLUMN()-1)/26)),"")&CHAR(65+MOD(COLUMN()-1,26)),"")&"1:"&IF(AND(COLUMN()>0,COLUMN()<257),IF(COLUMN()>26,CHAR(64+INT((COLUMN()-1)/26)),"")&CHAR(65+MOD(COLUMN()-1,26)),"")&ROW()-1)),"")
A4:A36	A4	=MONTH([@Datum])
B11	B11	=IF([@[Minuten gestreamd]]<>0,IF(A11=A10,MAX(INDIRECT(IF(AND(COLUMN()>0,COLUMN()<257),IF(COLUMN()>26,CHAR(64+INT((COLUMN()-1)/26)),"")&CHAR(65+MOD(COLUMN()-1,26)),"")&"1:"&IF(AND(COLUMN()>0,COLUMN()<257),IF(COLUMN()>26,CHAR(64+INT((COLUMN()-1)/26)),"")&CHAR(65+MOD(COLUMN()-1,26)),"")&ROW()-1))+1,1),"")
B12:B27,B29:B36	B12	=IF([@[Minuten gestreamd]]<>0,IF(A12=A11,VALUE(MAX(INDIRECT(IF(AND(COLUMN()>0,COLUMN()<257),IF(COLUMN()>26,CHAR(64+INT((COLUMN()-1)/26)),"")&CHAR(65+MOD(COLUMN()-1,26)),"")&"1:"&IF(AND(COLUMN()>0,COLUMN()<257),IF(COLUMN()>26,CHAR(64+INT((COLUMN()-1)/26)),"")&CHAR(65+MOD(COLUMN()-1,26)),"")&ROW()-1)))+1,1),"")
B28	B28	=IF([@[Minuten gestreamd]]<>0,MAXIFS(Stream,Maand,A28)+1,"")

 

[Zazu_0420]

We cannot manipulate data in a picture. Please upload a sample of your data using XL2BB. It would also be beneficial if you mocked up a solution based upon your sample. If you need instructions on XL2BB, see my signature block.

Thaanks!

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3	Maand	Stream X vd maand	Datum	Reclames (minuten)	Reclametijd (seconden) per uur	Gem. aantal kijkers	Chatberichten	Chatters	Clipweergaven	Clips gemaakt	Volgers erbij	Kijkers van hosts en raids (%)	Live weergaven	Maximaal aantal kijkers	Minuten bekeken	Minuten gestreamd
4	7		01/07/2020	0	0	0	30	2	12	0	11	0	0	0	0	0
5	7	1	02/07/2020	0	0	2927	755	39	2	0	25	0	540	39	7054	239
6	7		03/07/2020	0	0	0	2	2	0	0	3	0	0	0	0	0
7	7		04/07/2020	0	0	0	0	0	0	0	1	0	0	0	0	0
8	7		05/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
9	7		06/07/2020	0	0	0	0	0	0	0	1	0	0	0	0	0
10	7		07/07/2020	0	0	0	0	0	0	1	0	0	0	0	0	0
11	7	2	08/07/2020	0	0	12922	90	27	0	1	33	0	3726	216	16540	128
12	7	3	09/07/2020	0	0	8256	564	27	2	1	22	0	3431	168	16759	203
13	7	4	10/07/2020	0	0	16738	92	31	0	1	31	0	4954	235	21425	128
14	7		11/07/2020	0	0	0	0	0	1	0	2	0	1	0	0	0
15	7		12/07/2020	0	0	0	0	0	0	1	4	0	0	0	0	0
16	7		13/07/2020	0	0	0	0	0	0	0	4	0	0	0	0	0
17	7		14/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
18	7	5	15/07/2020	0	0	14354	451	43	0	3	27	0	4902	230	21961	153
19	7	6	16/07/2020	0	0	4995	355	30	0	2	12	1	1744	79	7243	145
20	7		17/07/2020	0	0	0	0	0	0	0	2	0	0	0	0	0
21	7		18/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
22	7		19/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
23	7		20/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
24	7		21/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
25	7	7	22/07/2020	0	0	196	533	46	0	1	21	2	7403	288	31752	162
26	7		23/07/2020	0	0	0	0	0	1	0	0	0	9	0	0	0
27	7		24/07/2020	0	0	0	0	0	10	2	0	0	0	0	0	0
28	7		25/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
29	7		26/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
30	7		27/07/2020	0	0	0	0	0	0	1	1	0	0	0	0	0
31	7		28/07/2020	0	0	0	0	0	0	0	0	0	0	0	0	0
32	7	8	29/07/2020	0	0	10755	602	64	0	2	40	0	3638	165	14627	142
33	7	9	30/07/2020	0	0	6557	667	30	1	1	16	0	3102	101	15671	239
34	7		31/07/2020	0	0	0	0	0	1	0	0	0	4	0	0	0
35	8	1	01/08/2020													2
36	8	2	02/08/2020													3
Main

Cell Formulas
Range	Formula
A4:A36	A4	=MONTH([@Datum])
B4:B36	B4	=IF([@[Minuten gestreamd]]=0,"",COUNTIFS(A$4:A4,[@Maand],P$4:P4,"<>0"))

 

[Zazu_0420]

Hey! Thanks. stupid of me not to think if countifs..
It seems like it is working! Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.