Counting number of times a maximum exceeds a threshold on a certain frequency

[varsakov]

Hi All, this is the first time I'm on this forum - I apologize in advance if my question isn't as detailed as it should be. Anyway, I was hoping someone could point me in the right direction for a macro I am trying to create.

I have a set of data that is as following: in Column A I have a list of months denoted by (10,11,12 for October, November, December respectively). 10 appears multiple times in the column on different rows to account for different days of october, the same applies to 11 (November) and 12 (December). In columns B through E I have different times of the days. So for instance, one column will have data for 1:00am on October 2nd, and another column will have data for 2:00am on October second. These day were randomly generated from 0 to 120.


Ultimately, I am hopping to create a macro that will spit out the sum of times in the three months mentioned where the maximum monthly figure of each month exceeded a threshold of say 100.

I am thinking it might need a loop of some sort to count the maximum of each month if it exceeds the threshold. Does anyone have any ideas on how to do this? I'm a beginner VBA coder and am trying to see some examples to become more advanced. I really appreciate the help!

 

[Aladin Akyurek]

Can be also done with formulas.

Why not post a small sample with data and the expected result?

 

[oldbrewer]

I do not understand what you want. What is the data in B2 to E10 What is the threshold. Do you want to count every time the threshold is exceeded and report that number for each month. Or do you want to see how many times the threshold was exceeded for EACH time?

 

[varsakov]

[TABLE="width: 918"]
<colgroup><col span="8"><col><col span="4"></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]1:00am[/TD]
[TD]2:00am[/TD]
[TD]3:00am[/TD]
[TD]4:00am[/TD]
[TD]Maximum on Each Day[/TD]
[TD]Maximum for the Month[/TD]
[TD][/TD]
[TD]Number of Times in the last three month the maximum monthly figures exceeded 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]112[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="colspan: 4"]This is the number I am looking to get with the Macro[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]101[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]110[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]112[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]93[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]85[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]111[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]87[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]86[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]40[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]99[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]83[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[varsakov]

Can be also done with formulas.

Why not post a small sample with data and the expected result?

Hi Aladin, Thank you for offering to take a look. I couldn't figure out a way to attach, so I have copy and pasted the table below:
[TABLE="width: 918"]
<colgroup><col span="8"><col><col span="4"></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]1:00am[/TD]
[TD]2:00am[/TD]
[TD]3:00am[/TD]
[TD]4:00am[/TD]
[TD]Maximum on Each Day[/TD]
[TD]Maximum for the Month[/TD]
[TD][/TD]
[TD]Number of Times in the last three month the maximum monthly figures exceeded 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]112[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="colspan: 4"]This is the number I am looking to get with the Macro[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]101[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]110[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]112[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]93[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]85[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]111[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]87[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]86[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]40[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]99[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]83[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Let me know if I can further clarify! Thank you.

 

[Aladin Akyurek]

Thanks for the exhibit and specs.

[TABLE="width: 432"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Max/If
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]100
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]29
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]101
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]101
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]112
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]10
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]65
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]91
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]101
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]8
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]101
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]112
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]11
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]110
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]92
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]78
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]99
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]110
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]112
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]12
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]112
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]16
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]49
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]90
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]112
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]112
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]14
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]53
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]75
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]90
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]90
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]2
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]80
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]51
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]12
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]70
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]80
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]93
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]11
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]5
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]41
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]93
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]85
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]71
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]28
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]85
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]22
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]8
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]26
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]36
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]56
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]47
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]87
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]87
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]57
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]50
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]77
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]77
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]37
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]86
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]86
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]28
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]86
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]38
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]7
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]40
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]23
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]40
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]99
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]80
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]20
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]56
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]99
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]22
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]10
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]83
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]65
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]83
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99
[/TD]
[TD="class: xl66, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[/TR]
</tbody>[/TABLE]

I1: 100 (the criterion value)

I2:I4, months (mont numbers) of interest.

G2, control+shift+enter, not just enter:

=MAX(IF($A$2:$A$16=A2,$B$2:$F$16))

I6, control+shift+enter:

=SUM((IF(FREQUENCY(IF(ISNUMBER(MATCH($A$2:$A$16,$I$2:$I$4,0)),
  MATCH($G$2:$G$16,$G$2:$G$16,0)),ROW($G$2:$G$16)-ROW($G$2)+1),
   $G$2:$G$16,-9.99999999999999E+307)>I1)+0)

 

[varsakov]

Thanks for the exhibit and specs.

[TABLE="width: 432"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Max/If[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]100[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]29[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]101[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]101[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]112[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]10[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]65[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]91[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]101[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]8[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]101[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]112[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]11[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]110[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]92[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]78[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]99[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]110[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]112[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]12[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]112[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]16[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]49[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]90[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]112[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]112[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]14[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]53[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]75[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]90[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]90[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]80[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]51[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]70[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]80[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]93[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]11[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]5[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]41[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]93[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]85[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]71[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]28[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]85[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]22[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]8[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]26[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]36[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]56[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]47[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]87[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]87[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]57[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]50[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]77[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]77[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]37[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]86[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]86[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]28[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]86[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]38[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]7[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]40[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]23[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]40[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]99[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]80[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]20[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]56[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]99[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]22[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]10[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]83[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]65[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]83[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]99[/TD]
[TD="class: xl66, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[/TR]
</tbody>[/TABLE]

I1: 100 (the criterion value)

I2:I4, months (mont numbers) of interest.

G2, control+shift+enter, not just enter:

=MAX(IF($A$2:$A$16=A2,$B$2:$F$16))

I6, control+shift+enter:

=SUM((IF(FREQUENCY(IF(ISNUMBER(MATCH($A$2:$A$16,$I$2:$I$4,0)),
  MATCH($G$2:$G$16,$G$2:$G$16,0)),ROW($G$2:$G$16)-ROW($G$2)+1),
   $G$2:$G$16,-9.99999999999999E+307)>I1)+0)

Thank you Aladin! I see your point of view.. I tried something similar to what you had above using Max, Offset, Match and Countif and returned correct numbers. No macro necessary I guess. Thanks for the help!!

 

[Aladin Akyurek]

Thank you Aladin! I see your point of view.. I tried something similar to what you had above using Max, Offset, Match and Countif and returned correct numbers. No macro necessary I guess. Thanks for the help!!

You are welcome.