Calculation MAX of variable reach

Zazu_0420

New Member
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.

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.

* =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))

Attachments

• Aantekening 2020-08-06 125520.png
74.1 KB · Views: 4

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

alansidman

Well-known Member
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

New Member
Problem file.xlsx
ABCDEFGHIJKLMNOP
3MaandStream X vd maandDatumReclames (minuten)Reclametijd (seconden) per uurGem. aantal kijkersChatberichtenChattersClipweergavenClips gemaaktVolgers erbijKijkers van hosts en raids (%)Live weergavenMaximaal aantal kijkersMinuten bekekenMinuten gestreamd
47#VERW!1/07/20200003021201100000
57#VERW!2/07/20200029277553920250540397054239
67#VERW!3/07/20200002200300000
774/07/20200000000100000
87#VERW!5/07/20200000000000000
97#VERW!6/07/20200000000100000
107#VERW!7/07/20200000001000000
117#VERW!8/07/20200012922902701330372621616540128
127#VERW!9/07/20200082565642721220343116816759203
137#VERW!10/07/20200016738923101310495423521425128
147 11/07/20200000010201000
157 12/07/20200000001400000
167 13/07/20200000000400000
177 14/07/20200000000000000
187#VERW!15/07/202000143544514303270490223021961153
197#VERW!16/07/202000499535530021211744797243145
207 17/07/20200000000200000
217 18/07/20200000000000000
227 19/07/20200000000000000
237 20/07/20200000000000000
247 21/07/20200000000000000
257#VERW!22/07/2020001965334601212740328831752162
267 23/07/20200000010009000
277 24/07/202000000102000000
287 25/07/20200000000000000
297 26/07/20200000000000000
307 27/07/20200000001100000
317 28/07/20200000000000000
327#VERW!29/07/202000107556026402400363816514627142
337#VERW!30/07/20200065576673011160310210115671239
347 31/07/20200000010004000
35811/08/20202
368#VERW!2/08/20203
Cell Formulas
RangeFormula
B4:B6,B8:B10B4=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:A36A4=MONTH([@Datum])
B11B11=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:B36B12=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),"")
B28B28=IF([@[Minuten gestreamd]]<>0,MAXIFS(Stream,Maand,A28)+1,"")

Zazu_0420

New Member
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

MrExcel MVP, Moderator

+Fluff New.xlsm
ABCDEFGHIJKLMNOP
1
2
3MaandStream X vd maandDatumReclames (minuten)Reclametijd (seconden) per uurGem. aantal kijkersChatberichtenChattersClipweergavenClips gemaaktVolgers erbijKijkers van hosts en raids (%)Live weergavenMaximaal aantal kijkersMinuten bekekenMinuten gestreamd
47 01/07/20200003021201100000
57102/07/20200029277553920250540397054239
67 03/07/20200002200300000
77 04/07/20200000000100000
87 05/07/20200000000000000
97 06/07/20200000000100000
107 07/07/20200000001000000
117208/07/20200012922902701330372621616540128
127309/07/20200082565642721220343116816759203
137410/07/20200016738923101310495423521425128
147 11/07/20200000010201000
157 12/07/20200000001400000
167 13/07/20200000000400000
177 14/07/20200000000000000
187515/07/202000143544514303270490223021961153
197616/07/202000499535530021211744797243145
207 17/07/20200000000200000
217 18/07/20200000000000000
227 19/07/20200000000000000
237 20/07/20200000000000000
247 21/07/20200000000000000
257722/07/2020001965334601212740328831752162
267 23/07/20200000010009000
277 24/07/202000000102000000
287 25/07/20200000000000000
297 26/07/20200000000000000
307 27/07/20200000001100000
317 28/07/20200000000000000
327829/07/202000107556026402400363816514627142
337930/07/20200065576673011160310210115671239
347 31/07/20200000010004000
358101/08/20202
368202/08/20203
Main
Cell Formulas
RangeFormula
A4:A36A4=MONTH([@Datum])
B4:B36B4=IF([@[Minuten gestreamd]]=0,"",COUNTIFS(A\$4:A4,[@Maand],P\$4:P4,"<>0"))

Zazu_0420

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

Fluff

MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
119
Replies
3
Views
168
Replies
0
Views
217
Replies
6
Views
186
Replies
4
Views
67

1,127,161
Messages
5,623,096
Members
415,953
Latest member
Balloonman4343

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back