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

#### varsakov

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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

##### MrExcel MVP
Can be also done with formulas.

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

#### oldbrewer

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

##### New Member
 Month 1:00am 2:00am 3:00am 4:00am Maximum on Each Day Maximum for the Month Number of Times in the last three month the maximum monthly figures exceeded 100 10 29 1 4 101 101 112 2 This is the number I am looking to get with the Macro 10 65 91 101 8 101 10 110 92 78 99 110 10 112 16 49 90 112 11 14 53 75 90 90 111 11 80 51 12 70 80 11 93 11 5 41 93 11 85 71 28 1 85 11 22 111 8 26 111 11 36 56 47 87 87 12 4 57 50 77 77 99 12 37 86 86 28 86 12 38 7 40 23 40 12 99 80 20 56 99 12 22 10 83 65 83

<colgroup><col span="8"><col><col span="4"></colgroup><tbody>
</tbody>

#### varsakov

##### New Member

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:
 Month 1:00am 2:00am 3:00am 4:00am Maximum on Each Day Maximum for the Month Number of Times in the last three month the maximum monthly figures exceeded 100 10 29 1 4 101 101 112 2 This is the number I am looking to get with the Macro 10 65 91 101 8 101 10 110 92 78 99 110 10 112 16 49 90 112 11 14 53 75 90 90 111 11 80 51 12 70 80 11 93 11 5 41 93 11 85 71 28 1 85 11 22 111 8 26 111 11 36 56 47 87 87 12 4 57 50 77 77 99 12 37 86 86 28 86 12 38 7 40 23 40 12 99 80 20 56 99 12 22 10 83 65 83

<colgroup><col span="8"><col><col span="4"></colgroup><tbody>
</tbody>
Let me know if I can further clarify! Thank you.

##### MrExcel MVP
Thanks for the exhibit and specs.

 Max/If 100 10 29 1 4 101 101 112 10 10 65 91 101 8 101 112 11 10 110 92 78 99 110 112 12 10 112 16 49 90 112 112 11 14 53 75 90 90 111 2 11 80 51 12 70 80 111 11 93 11 5 41 93 111 11 85 71 28 1 85 111 11 22 111 8 26 111 111 11 36 56 47 87 87 111 12 4 57 50 77 77 99 12 37 86 86 28 86 99 12 38 7 40 23 40 99 12 99 80 20 56 99 99 12 22 10 83 65 83 99

<tbody>
</tbody>

I1: 100 (the criterion value)

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

G2, control+shift+enter, not just enter:
Rich (BB code):
``=MAX(IF(\$A\$2:\$A\$16=A2,\$B\$2:\$F\$16))``
I6, control+shift+enter:
Rich (BB code):
``````=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

##### New Member
Thanks for the exhibit and specs.

 Max/If 100 10 29 1 4 101 101 112 10 10 65 91 101 8 101 112 11 10 110 92 78 99 110 112 12 10 112 16 49 90 112 112 11 14 53 75 90 90 111 2 11 80 51 12 70 80 111 11 93 11 5 41 93 111 11 85 71 28 1 85 111 11 22 111 8 26 111 111 11 36 56 47 87 87 111 12 4 57 50 77 77 99 12 37 86 86 28 86 99 12 38 7 40 23 40 99 12 99 80 20 56 99 99 12 22 10 83 65 83 99

<tbody>
</tbody>

I1: 100 (the criterion value)

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

G2, control+shift+enter, not just enter:
Rich (BB code):
``=MAX(IF(\$A\$2:\$A\$16=A2,\$B\$2:\$F\$16))``
I6, control+shift+enter:
Rich (BB code):
``````=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!!

##### MrExcel MVP
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.

Replies
5
Views
159
Replies
9
Views
155
Replies
1
Views
100
Replies
3
Views
280
Replies
1
Views
78

1,127,063
Messages
5,622,470
Members
415,897
Latest member
Barry18180

### 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.

### Which adblocker are you using?

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