EXCEL: multiple criteria, only one count per row

J

jakulski

New Member
Joined
Jan 17, 2017
Messages
15
Hello. Within an Excel workbook I have a worksheet that has 30 columns and more than 50,000 rows. Below, I’ve have copied 7 of the columns and 29 of the rows (not including the two column heading rows). Let’s call this worksheet 201617. On another worksheet (also below), which I will call Summary, I need to show the following:
  • for schools in a given region, the cumulative number of times that TB* (TB1, TB2, TB3, TB4, TB5) was used as an entry (regardless of how many times per incident);
of the 29 incidents listed, the total number of TB* entries was 36
  • for each school, the number of times TB* was used as an offense entry; and
Examples: Anywhere High = 11entries; Somewhere High = 5 entries
  • for each school, the number of incidents in which TB* was included as one or more of the offense entries.
Example: Anywhere High = 7 incidents in which TB* was used; Somewhere High = 4 incidents in which TB* was used

I need help on the second and third bullets above. I’ve tried coutnifs (definitely won’t work for the third bullet because it doesn’t work when wanting to count unduplicated info), sumifs, sumproduct, etc., but without luck. And though I’m pretty skilled with Excel, I’m still figuring out VBA, arrays, etc. Please help…and thank you in advance!

[TABLE="width: 533"]
<tbody>[TR]
[TD]colA
[/TD]
[TD]colB
[/TD]
[TD]colC
[/TD]
[TD]colD
[/TD]
[TD]colE
[/TD]
[TD]colF
[/TD]
[TD]colG
[/TD]
[/TR]
[TR]
[TD]School
[/TD]
[TD]ID
[/TD]
[TD]Name
[/TD]
[TD]PriOff
[/TD]
[TD]PriOff2
[/TD]
[TD]PriOff3
[/TD]
[TD]Reg
[/TD]
[/TR]
[TR]
[TD]Anywhere Elem
[/TD]
[TD]1234569
[/TD]
[TD]Nakia
[/TD]
[TD]WP5
[/TD]
[TD]TB2
[/TD]
[TD]DR5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Elem
[/TD]
[TD]1234576
[/TD]
[TD]Barry
[/TD]
[TD]TB1
[/TD]
[TD]BU1
[/TD]
[TD]TB5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Elem
[/TD]
[TD]1234585
[/TD]
[TD]Sharon
[/TD]
[TD]TB1
[/TD]
[TD]TB5
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234573
[/TD]
[TD]Alexei
[/TD]
[TD]DC6
[/TD]
[TD]TB2
[/TD]
[TD]TB4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234581
[/TD]
[TD]Gregory
[/TD]
[TD]TB2
[/TD]
[TD]DC2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234567
[/TD]
[TD]Asia
[/TD]
[TD]TB5
[/TD]
[TD]DR5
[/TD]
[TD]AT5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234594
[/TD]
[TD]Robert
[/TD]
[TD]AL1
[/TD]
[TD]AL2
[/TD]
[TD]AL4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234582
[/TD]
[TD]Leah
[/TD]
[TD]TB1
[/TD]
[TD]TB2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234588
[/TD]
[TD]Luciano
[/TD]
[TD]TB4
[/TD]
[TD]BA1
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234574
[/TD]
[TD]Alexei
[/TD]
[TD]DR8
[/TD]
[TD]DC9
[/TD]
[TD]TB4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234589
[/TD]
[TD]Mahemuti
[/TD]
[TD]DC4
[/TD]
[TD]BA2
[/TD]
[TD]RT1
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234580
[/TD]
[TD]Efrain
[/TD]
[TD]TB1
[/TD]
[TD]TB5
[/TD]
[TD]TB2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Middle
[/TD]
[TD]1234584
[/TD]
[TD]Keisha
[/TD]
[TD]TB1
[/TD]
[TD]TB5
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Middle
[/TD]
[TD]1234575
[/TD]
[TD]Bo Sun
[/TD]
[TD]TB1
[/TD]
[TD]TB2
[/TD]
[TD]TB4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Middle
[/TD]
[TD]1234568
[/TD]
[TD]Lawrence
[/TD]
[TD]TB2
[/TD]
[TD]TB5
[/TD]
[TD]TB4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Middle
[/TD]
[TD]1234591
[/TD]
[TD]Mohammad
[/TD]
[TD]DC4
[/TD]
[TD]BU2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234595
[/TD]
[TD]Tayelor
[/TD]
[TD]HO1
[/TD]
[TD]AL2
[/TD]
[TD]AL4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234593
[/TD]
[TD]Barbara
[/TD]
[TD]AL1
[/TD]
[TD]AT1
[/TD]
[TD]AL4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234579
[/TD]
[TD]Douglas
[/TD]
[TD]TB1
[/TD]
[TD]TB2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234587
[/TD]
[TD]Leenah
[/TD]
[TD]RO1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234583
[/TD]
[TD]Kyle
[/TD]
[TD]TB1
[/TD]
[TD]TB2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234572
[/TD]
[TD]Alexys
[/TD]
[TD]WX5
[/TD]
[TD]DR8
[/TD]
[TD]TB2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere High
[/TD]
[TD]1234577
[/TD]
[TD]Brandon
[/TD]
[TD]TB2
[/TD]
[TD]AT5
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere High
[/TD]
[TD]1234570
[/TD]
[TD]Armani
[/TD]
[TD]DP1
[/TD]
[TD]AL2
[/TD]
[TD]TB2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere High
[/TD]
[TD]1234590
[/TD]
[TD]Mia
[/TD]
[TD]TB5
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere High
[/TD]
[TD]1234586
[/TD]
[TD]Holly
[/TD]
[TD]TB1
[/TD]
[TD]TB5
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Middle
[/TD]
[TD]1234592
[/TD]
[TD]Rella
[/TD]
[TD]AL1
[/TD]
[TD]AL2
[/TD]
[TD]AL4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Middle
[/TD]
[TD]1234571
[/TD]
[TD]Ann
[/TD]
[TD]DR8
[/TD]
[TD]DP1
[/TD]
[TD]TB2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Middle
[/TD]
[TD]1234578
[/TD]
[TD]Diego
[/TD]
[TD]TB2
[/TD]
[TD]AT5
[/TD]
[TD]
[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
<strike>
</strike>
[TABLE="width: 784"]
<tbody>[TR]
[TD="colspan: 17"]Summary[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="align: center"]# of
Inc
[/TD]
[TD="colspan: 3, align: center"]TB1
[/TD]
[TD="colspan: 3, align: center"]TB2
[/TD]
[TD="colspan: 3, align: center"]TB3
[/TD]
[TD="colspan: 3, align: center"]TB4
[/TD]
[TD="colspan: 3, align: center"]TB5
[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]PriOff
[/TD]
[TD="align: center"]PriOff2
[/TD]
[TD="align: center"]PriOff3
[/TD]
[TD="align: center"]PriOff
[/TD]
[TD="align: center"]PriOff2
[/TD]
[TD="align: center"]PriOff3
[/TD]
[TD="align: center"]PriOff
[/TD]
[TD="align: center"]PriOff2
[/TD]
[TD="align: center"]PriOff3
[/TD]
[TD="align: center"]PriOff
[/TD]
[TD="align: center"]PriOff2
[/TD]
[TD="align: center"]PriOff3
[/TD]
[TD="align: center"]PriOff
[/TD]
[TD="align: center"]PriOff2
[/TD]
[TD="align: center"]PriOff3
[/TD]
[/TR]
[TR]
[TD="align: right"]Anywhere Elem
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]Anywhere Middle
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: right"]Anywhere High
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: right"]Somewhere Elem
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: right"]Somewhere Middle
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: right"]Somewhere High
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 784"]
<tbody>[TR]
[TD="colspan: 17"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
NOTE: THIS IS A CORRECTED POST -THE FORMATTING WAS OFF ON THE SECOND CHART (NOT THAT IT'S SO HOT NOW, BUT IT'S THE BEST I COULD DO WITH THE FORMATTING OPTIONS AVAILABLE)

Hello. Within an Excel workbook I have a worksheet that has 30 columns and more than 50,000 rows. Below, I’ve have copied 7 of the columns and 29 of the rows (not including the two column heading rows). Let’s call this worksheet 201617. On another worksheet (also below), which I will call Summary, I need to show the following:

  • for schools in a given region, the cumulative number of times that TB* (TB1, TB2, TB3, TB4, TB5) was used as an entry (regardless of how many times per incident);
of the 29 incidents listed, the total number of TB* entries was 36

  • for each school, the number of times TB* was used as an offense entry; and
Examples: Anywhere High = 11entries; Somewhere High = 5 entries

  • for each school, the number of incidents in which TB* was included as one or more of the offense entries.
Example: Anywhere High = 7 incidents in which TB* was used; Somewhere High = 4 incidents in which TB* was used

I need help on the second and third bullets above. I’ve tried coutnifs (definitely won’t work for the third bullet because it doesn’t work when wanting to count unduplicated info), sumifs, sumproduct, etc., but without luck. And though I’m pretty skilled with Excel, I’m still figuring out VBA, arrays, etc. Please help…and thank you in advance!

[TABLE="class: cms_table, width: 533"]
<tbody>[TR]
[TD]colA
[/TD]
[TD]colB
[/TD]
[TD]colC
[/TD]
[TD]colD
[/TD]
[TD]colE
[/TD]
[TD]colF
[/TD]
[TD]colG
[/TD]
[/TR]
[TR]
[TD]School
[/TD]
[TD]ID
[/TD]
[TD]Name
[/TD]
[TD]PriOff
[/TD]
[TD]PriOff2
[/TD]
[TD]PriOff3
[/TD]
[TD]Reg
[/TD]
[/TR]
[TR]
[TD]Anywhere Elem
[/TD]
[TD]1234569
[/TD]
[TD]Nakia
[/TD]
[TD]WP5
[/TD]
[TD]TB2
[/TD]
[TD]DR5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Elem
[/TD]
[TD]1234576
[/TD]
[TD]Barry
[/TD]
[TD]TB1
[/TD]
[TD]BU1
[/TD]
[TD]TB5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Elem
[/TD]
[TD]1234585
[/TD]
[TD]Sharon
[/TD]
[TD]TB1
[/TD]
[TD]TB5
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234573
[/TD]
[TD]Alexei
[/TD]
[TD]DC6
[/TD]
[TD]TB2
[/TD]
[TD]TB4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234581
[/TD]
[TD]Gregory
[/TD]
[TD]TB2
[/TD]
[TD]DC2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234567
[/TD]
[TD]Asia
[/TD]
[TD]TB5
[/TD]
[TD]DR5
[/TD]
[TD]AT5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234594
[/TD]
[TD]Robert
[/TD]
[TD]AL1
[/TD]
[TD]AL2
[/TD]
[TD]AL4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234582
[/TD]
[TD]Leah
[/TD]
[TD]TB1
[/TD]
[TD]TB2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234588
[/TD]
[TD]Luciano
[/TD]
[TD]TB4
[/TD]
[TD]BA1
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234574
[/TD]
[TD]Alexei
[/TD]
[TD]DR8
[/TD]
[TD]DC9
[/TD]
[TD]TB4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234589
[/TD]
[TD]Mahemuti
[/TD]
[TD]DC4
[/TD]
[TD]BA2
[/TD]
[TD]RT1
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]1234580
[/TD]
[TD]Efrain
[/TD]
[TD]TB1
[/TD]
[TD]TB5
[/TD]
[TD]TB2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Middle
[/TD]
[TD]1234584
[/TD]
[TD]Keisha
[/TD]
[TD]TB1
[/TD]
[TD]TB5
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Middle
[/TD]
[TD]1234575
[/TD]
[TD]Bo Sun
[/TD]
[TD]TB1
[/TD]
[TD]TB2
[/TD]
[TD]TB4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Middle
[/TD]
[TD]1234568
[/TD]
[TD]Lawrence
[/TD]
[TD]TB2
[/TD]
[TD]TB5
[/TD]
[TD]TB4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Anywhere Middle
[/TD]
[TD]1234591
[/TD]
[TD]Mohammad
[/TD]
[TD]DC4
[/TD]
[TD]BU2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234595
[/TD]
[TD]Tayelor
[/TD]
[TD]HO1
[/TD]
[TD]AL2
[/TD]
[TD]AL4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234593
[/TD]
[TD]Barbara
[/TD]
[TD]AL1
[/TD]
[TD]AT1
[/TD]
[TD]AL4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234579
[/TD]
[TD]Douglas
[/TD]
[TD]TB1
[/TD]
[TD]TB2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234587
[/TD]
[TD]Leenah
[/TD]
[TD]RO1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234583
[/TD]
[TD]Kyle
[/TD]
[TD]TB1
[/TD]
[TD]TB2
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]1234572
[/TD]
[TD]Alexys
[/TD]
[TD]WX5
[/TD]
[TD]DR8
[/TD]
[TD]TB2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere High
[/TD]
[TD]1234577
[/TD]
[TD]Brandon
[/TD]
[TD]TB2
[/TD]
[TD]AT5
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere High
[/TD]
[TD]1234570
[/TD]
[TD]Armani
[/TD]
[TD]DP1
[/TD]
[TD]AL2
[/TD]
[TD]TB2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere High
[/TD]
[TD]1234590
[/TD]
[TD]Mia
[/TD]
[TD]TB5
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere High
[/TD]
[TD]1234586
[/TD]
[TD]Holly
[/TD]
[TD]TB1
[/TD]
[TD]TB5
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Middle
[/TD]
[TD]1234592
[/TD]
[TD]Rella
[/TD]
[TD]AL1
[/TD]
[TD]AL2
[/TD]
[TD]AL4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Middle
[/TD]
[TD]1234571
[/TD]
[TD]Ann
[/TD]
[TD]DR8
[/TD]
[TD]DP1
[/TD]
[TD]TB2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Somewhere Middle
[/TD]
[TD]1234578
[/TD]
[TD]Diego
[/TD]
[TD]TB2
[/TD]
[TD]AT5
[/TD]
[TD]
[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 784"]
<tbody>[TR]
[TD="colspan: 17"]Summary
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD="colspan: 3"]TB1 TB1 TB1
[/TD]
[TD="colspan: 3"]TB2 TB2 TB2
[/TD]
[TD="colspan: 3"]TB3 TB3 TB3
[/TD]
[TD="colspan: 3"]TB4 TB4 TB4
[/TD]
[TD="colspan: 3"]TB5 TB5 TB5
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]# of
Inc
[/TD]
[TD]PriOff
[/TD]
[TD]PriOff2
[/TD]
[TD]PriOff3[/TD]
[TD]PriOff
[/TD]
[TD]PriOff2
[/TD]
[TD]PriOff3
[/TD]
[TD]PriOff
[/TD]
[TD]PriOff2
[/TD]
[TD]PriOff3
[/TD]
[TD]PriOff
[/TD]
[TD]PriOff2
[/TD]
[TD]PriOff3
[/TD]
[TD]PriOff
[/TD]
[TD]PriOff2
[/TD]
[TD]PriOff3
[/TD]
[/TR]
[TR]
[TD]Anywhere Elem
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Anywhere Middle
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]Anywhere High
[/TD]
[TD]9
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]Somewhere Elem
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/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]Somewhere Middle
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/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]Somewhere High
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You didn't say how you generated your Summary table results so far. I created a formula (C4) that creates those totals. Drag the formula down and to the right as needed. The tricky part is determining the TB1, TB2, etc. that is needed. I came up with a tricky OFFSET function to do it, but it really would be easier to just put "TB1" in C1:E1, and format C1 and E1 so that it is hidden.

ABCDEFGHIJKLMNOPQRS

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]TB1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]TB2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]TB3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]TB4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]TB5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]# of inc[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]PriOff3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Total TB* offenses[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Anywhere Elem[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Anywhere Middle[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Anywhere High[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Somewhere Elem[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Somewhere Middle[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Somewhere High[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Total for region[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=COUNTIFS('201617'!$A:$A,$A4,INDEX('201617'!$D:$F,0,MATCH(C$3,'201617'!$D$1:$F$1,0)),OFFSET($C$1,,INT((COLUMNS($C4:C4)-1)/3)*3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S4[/TH]
[TD="align: left"]=SUM(C4:Q4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B12[/TH]
[TD="align: left"]=SUM(C4:Q9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]{=SUM(('201617'!$A$2:$A$30=A4)*(MMULT((LEFT('201617'!$D$2:$F$30,2)="TB")+0,{1;1;1})>0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Given that table, your bullet points 1 and 2 are easy. Total for the region in in B12, and the total by school is in S4 and down.

Bullet point 3 is trickier. I used a MMULT function to act as an OR. It works well, but it may bog your sheet down a lot if you have 30,000 rows. Hope this helps, let us know.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
COUNTIFS or SUMPRODUCT?
Replies
1
Views
515
thisoldman
T
C
  • Question Question
Advanced help needed: Refinement of clumsy code
Replies
1
Views
878
carcharoth2554
C

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

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