Count School, Month, and Frequency

S

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
1,168
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good evening I have a sample of data below. I have about 200,000 rows. I am looking for a formula or subroutine that will find the school month and calculate the frequency like below. Here is an example I would like to check Lincoln School in the SEPT column and count how many cells are 1 to 4. In the example it would be 4. Any help would be appreciated.
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>STUDENT NUMBER</th><th>SCHOOL</th><th>SCHOOLID</th><th>SEPT</th><th>OCT</th><th>NOV</th><th>DEC</th><th>JAN</th><th>FEB</th><th>MAR</th><th>APR</th><th>MAY</th><th>JUN</th></tr></thead><tbody>
<tr><td>1</td><td>Lincoln School</td><td>111</td><td>13</td><td>12</td><td>3</td><td>1</td><td>1</td><td>2</td><td>4</td><td>6</td><td>8</td><td>2</td></tr>
<tr><td>2</td><td>Madison High School</td><td>333</td><td>9</td><td>5</td><td>4</td><td>11</td><td>8</td><td>5</td><td>2</td><td>1</td><td>2</td><td>0</td></tr>
<tr><td>3</td><td>Madison High School</td><td>333</td><td>4</td><td>6</td><td>3</td><td>0</td><td>8</td><td>7</td><td>4</td><td>3</td><td>3</td><td>1</td></tr>
<tr><td>4</td><td>Lincoln School</td><td>111</td><td>3</td><td>3</td><td>4</td><td>3</td><td>3</td><td>4</td><td>9</td><td>0</td><td>10</td><td>6</td></tr>
<tr><td>5</td><td>Madison High School</td><td>333</td><td>4</td><td>3</td><td>4</td><td>5</td><td>1</td><td>6</td><td>8</td><td>5</td><td>12</td><td>4</td></tr>
<tr><td>6</td><td>Madison High School</td><td>333</td><td>2</td><td>0</td><td>1</td><td>1</td><td>2</td><td>1</td><td>0</td><td>0</td><td>11</td><td>3</td></tr>
<tr><td>7</td><td>Lincoln School</td><td>111</td><td>4</td><td>4</td><td>9</td><td>10</td><td>14</td><td>11</td><td>18</td><td>13</td><td>21</td><td>9</td></tr>
<tr><td>8</td><td>Lincoln School</td><td>111</td><td>7</td><td>1</td><td>2</td><td>4</td><td>4</td><td>15</td><td>20</td><td>14</td><td>19</td><td>6</td></tr>
<tr><td>9</td><td>Johnson High School</td><td>222</td><td>2</td><td>1</td><td>2</td><td>5</td><td>1</td><td>5</td><td>4</td><td>10</td><td>3</td><td>0</td></tr>
<tr><td>10</td><td>Madison High School</td><td>333</td><td>0</td><td>1</td><td>0</td><td>1</td><td>12</td><td>0</td><td>5</td><td>4</td><td>7</td><td>12</td></tr>
<tr><td>11</td><td>Johnson High School</td><td>222</td><td>2</td><td>2</td><td>3</td><td>3</td><td>1</td><td>1</td><td>4</td><td>6</td><td>2</td><td>0</td></tr>
<tr><td>12</td><td>Johnson High School</td><td>222</td><td>3</td><td>1</td><td>3</td><td>1</td><td>3</td><td>5</td><td>8</td><td>6</td><td>12</td><td>7</td></tr>
<tr><td>13</td><td>Madison High School</td><td>333</td><td>8</td><td>7</td><td>4</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>14</td><td>Lincoln School</td><td>111</td><td>0</td><td>3</td><td>6</td><td>6</td><td>0</td><td>0</td><td>4</td><td>2</td><td>4</td><td>5</td></tr>
<tr><td>15</td><td>Madison High School</td><td>333</td><td>0</td><td>1</td><td>1</td><td>2</td><td>1</td><td>3</td><td>0</td><td>0</td><td>8</td><td>3</td></tr>
<tr><td>16</td><td>Lincoln School</td><td>111</td><td>1</td><td>1</td><td>1</td><td>8</td><td>7</td><td>4</td><td>0</td><td>0</td><td>1</td><td>2</td></tr>
<tr><td>17</td><td>Madison High School</td><td>333</td><td>2</td><td>2</td><td>2</td><td>4</td><td>1</td><td>1</td><td>3</td><td>2</td><td>2</td><td>2</td></tr>
<tr><td>18</td><td>Johnson High School</td><td>222</td><td>6</td><td>5</td><td>8</td><td>7</td><td>2</td><td>5</td><td>3</td><td>4</td><td>5</td><td>8</td></tr>
<tr><td>19</td><td>Lincoln School</td><td>111</td><td>1</td><td>1</td><td>10</td><td>2</td><td>2</td><td>0</td><td>1</td><td>0</td><td>6</td><td>2</td></tr>
</tbody></table>
This is what I am looking for:
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>SCHOOL</th><th> </th><th>1-4</th><th>5-10</th><th>11-15</th><th>16+</th></tr></thead><tbody>
<tr><td>Lincoln School</td><td>SEPT</td><td>4</td><td>1</td><td>1</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>OCT</td><td>6</td><td>0</td><td>1</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>NOV</td><td>4</td><td>3</td><td>0</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>DEC</td><td>4</td><td>3</td><td>0</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>JAN</td><td>4</td><td>1</td><td>1</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>FEB</td><td>3</td><td>0</td><td>2</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>MAR</td><td>3</td><td>1</td><td>0</td><td>2</td></tr>
<tr><td>Lincoln School</td><td>APR</td><td>1</td><td>1</td><td>2</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>MAY</td><td>2</td><td>3</td><td>0</td><td>2</td></tr>
<tr><td>Lincoln School</td><td>JUN</td><td>3</td><td>4</td><td>0</td><td>0</td></tr>
</tbody></table>
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Start here assuming your range of data starts in A1. Modify as necessary

=COUNTIFS($B$2:$B$20,B2,$D$2:$D$20,">0",$D$2:$D$20,"<5"), this will give you the result for Sept, Lincoln between 0 and 5
 
Last edited:
Upvote 0
yeah thanks alansideman I was using =SUMPRODUCT(($B$2:$B$20=$O2)*($D$2:$D$20={1,2,3,4})) but the problem I have is that when I go to pull down the months I need the formula to update. The same will occur with your formula,
 
Upvote 0
Stephen_IV said:
I have about 200,000 rows. I am looking for a formula or subroutine ...
Click to expand...
Given the size of the data, I suspect any formula solution may be very slow to calculate, hence I'm suggesting a macro (though I haven't tested it with that much data either).

Code: 
Sub CountRanges()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long, cols As Long, baseRow As Long, col As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("B1", Range("M" & Rows.Count).End(xlUp)).Value
  uba2 = UBound(a, 2)
  cols = uba2 - 2
  ReDim b(1 To Rows.Count, 1 To 6)
  
  For i = 2 To UBound(a)
    If Not d.exists(a(i, 1)) Then
      For k = 1 To cols
        b(d.Count * cols + k, 1) = a(i, 1)
        b(d.Count * cols + k, 2) = a(1, k + 2)
      Next k
      d(a(i, 1)) = d.Count
    End If
    baseRow = d(a(i, 1)) * cols + 1
    For j = 3 To uba2
      Select Case a(i, j)
        Case Is >= 16: col = 6
        Case Is >= 11: col = 5
        Case Is >= 5: col = 4
        Case Is >= 1: col = 3
        Case Else: col = 0
      End Select
      If col > 0 Then b(baseRow + j - 3, col) = b(baseRow + j - 3, col) + 1
    Next j
  Next i
  Application.ScreenUpdating = False
  With Range("O1").Resize(d.Count * cols + 1, UBound(b, 2))
    .Offset(1).Resize(.Rows.Count - 1).Value = b
    .SpecialCells(xlBlanks).Value = 0
    With .Rows(1)
      .NumberFormat = "@"
      .Value = Array("School", "Month", "1-4", "5-10", "11-15", "16+")
    End With
    .Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

My sample data & results:


Book1
BCDEFGHIJKLMNOPQRST
1SCHOOLSCHOOLIDSEPTOCTNOVDECJANFEBMARAPRMAYJUNSchoolMonth1-45-1011-1516+
2Lincoln School111131231124682Lincoln SchoolSEPT4110
3Madison High School33395411852120Lincoln SchoolOCT6010
4Madison High School3334630874331Lincoln SchoolNOV4300
5Lincoln School11133433490106Lincoln SchoolDEC4300
6Madison High School33343451685124Lincoln SchoolJAN4110
7Madison High School33320112100113Lincoln SchoolFEB3020
8Lincoln School1114491014111813219Lincoln SchoolMAR3102
9Lincoln School11171244152014196Lincoln SchoolAPR1120
10Johnson High School22221251541030Lincoln SchoolMAY2302
11Madison High School333010112054712Lincoln SchoolJUN3400
12Johnson High School2222233114620Madison High SchoolSEPT4200
13Johnson High School22231313586127Madison High SchoolOCT4300
14Madison High School3338740000000Madison High SchoolNOV7000
15Lincoln School1110366004245Madison High SchoolDEC4110
16Madison High School3330112130083Madison High SchoolJAN4210
17Lincoln School1111118740012Madison High SchoolFEB3300
18Madison High School3332224113222Madison High SchoolMAR3200
19Johnson High School2226587253458Madison High SchoolAPR4100
20Lincoln School11111102201062Madison High SchoolMAY3220
21Madison High SchoolJUN5010
22Johnson High SchoolSEPT3100
23Johnson High SchoolOCT3100
24Johnson High SchoolNOV3100
25Johnson High SchoolDEC2200
26Johnson High SchoolJAN4000
27Johnson High SchoolFEB1300
28Johnson High SchoolMAR3100
29Johnson High SchoolAPR1300
30Johnson High SchoolMAY2110
31Johnson High SchoolJUN0200
Count
 
Last edited:
Upvote 0
Hi,

I am sure Peter's vba code works faster given the size of the data. If you still need a formula, you may try this -

=COUNTIFS($B$2:$B$20,$B26,OFFSET($C$1,1,MATCH($C26,$D$1:$M$1,0),ROWS($B$2:$B$20),1),">=1",OFFSET($C$1,1,MATCH($C26,$D$1:$M$1,0),ROWS($B$2:$B$20),1),"<=4")


Assuming data is in A1:M20 and output range is B25:G35

The formula can be copied for a column and needs to be updated for other conditions: 5-10, 11-15 and 16+. This hard coding can also be avoided by using helper cells. Hope this helps.

Regards.
 
Upvote 0
Peter_SSs absolutely beautiful!!!! Worked like a charm!!! I appreciate this! Thank you very much!
 
Upvote 0
pkdeimos, Thank you also! For your contribution into this post!
 
Upvote 0
Stephen_IV said:
Peter_SSs absolutely beautiful!!!! Worked like a charm!!! I appreciate this! Thank you very much!
Click to expand...
You are very welcome. :)


If you did want to try a formula solution, and the school/month columns were pre-populated, you could try this SUMPRODUCT formula. It should be less onerous than using OFFSET.
Q1:T1 house the lower bound of each range.
Formula in Q2 is copied across to T2 and down
Column U is empty (at least no numerical values)


Book1
BCDEFGHIJKLMNOPQRST
1SCHOOLSCHOOLIDSEPTOCTNOVDECJANFEBMARAPRMAYJUNSchoolMonth151116
2Lincoln School111131231124682Lincoln SchoolSEPT4110
3Madison High School33395411852120Lincoln SchoolOCT6010
4Madison High School3334630874331Lincoln SchoolNOV4300
5Lincoln School11133433490106Lincoln SchoolDEC4300
6Madison High School33343451685124Lincoln SchoolJAN4110
7Madison High School33320112100113Lincoln SchoolFEB3020
8Lincoln School1114491014111813219Lincoln SchoolMAR3102
9Lincoln School11171244152014196Lincoln SchoolAPR1120
10Johnson High School22221251541030Lincoln SchoolMAY2302
11Madison High School333010112054712Lincoln SchoolJUN3400
12Johnson High School2222233114620Madison High SchoolSEPT4200
13Johnson High School22231313586127Madison High SchoolOCT4300
14Madison High School3338740000000Madison High SchoolNOV7000
15Lincoln School1110366004245Madison High SchoolDEC4110
16Madison High School3330112130083Madison High SchoolJAN4210
17Lincoln School1111118740012Madison High SchoolFEB3300
18Madison High School3332224113222Madison High SchoolMAR3200
19Johnson High School2226587253458Madison High SchoolAPR4100
20Lincoln School11111102201062Madison High SchoolMAY3220
21Madison High SchoolJUN5010
22Johnson High SchoolSEPT3100
Count (2)
Cell Formulas
RangeFormula
Q2=SUMPRODUCT(($B$2:$B$20=$O2)*($D$1:$M$1=$P2)*($D$2:$M$20>=Q$1))-SUM(R2:$U2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Distinct count based on 2 columns in a Pivot Table
Replies
0
Views
232
leefletcher
L
D
  • Solved
Compare two ranges, highlight cell with text in one range missing from other
Replies
6
Views
128
Peter_SSs
Peter_SSs
N
  • Question
Find Latest comment for ID using vlookup/MAX?
Replies
1
Views
89
KOKOSEK
KOKOSEK
T
  • Question
cross country meet scoring
Replies
7
Views
118
Joe4
Joe4
D
  • Solved
Find a cell in a table, and return the value above
Replies
3
Views
66
Alex Blakenburg
A

Forum statistics

Threads
1,214,975
Messages
6,122,538
Members
449,088
Latest member
RandomExceller01

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top