Count occurances and tabulate assessment averages from a multi-variable array

A

AdMission

New Member
Joined
Mar 22, 2018
Messages
15
Hello,
I am completely new to this forum and was hoping someone can help me figure out a convenient way to do help in tabulating data from a large scale interviewing process. Essentially, there are specific 4 questions (Q1, Q2, Q3, and Q4) that can asked by different individuals. I would like to first count how many questions (and which specific question) was asked by each interviewer. Additionally, I would like to know the average score that is given by each interviewer for each numbered question asked, as well as an overall average for each interviewer for all the questions they asked. Here is a sample of the data I have.
[TABLE="width: 213"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Q#[/TD]
[TD="class: xl66, width: 111"]Interviewer[/TD]
[TD="class: xl67, width: 87"]Score[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q1[/TD]
[TD="class: xl69"]YJ[/TD]
[TD="class: xl70"]2[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q2[/TD]
[TD="class: xl69"]MB[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl71"]Q3[/TD]
[TD="class: xl69"]HB[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q4[/TD]
[TD="class: xl69"]YJ[/TD]
[TD="class: xl70"]2[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q1[/TD]
[TD="class: xl69"]LT[/TD]
[TD="class: xl70"]4[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q2[/TD]
[TD="class: xl69"]PK[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl71"]Q3[/TD]
[TD="class: xl69"]MJ[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q4[/TD]
[TD="class: xl69"]QK[/TD]
[TD="class: xl70"]4[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q1[/TD]
[TD="class: xl69"]QK[/TD]
[TD="class: xl70"]4[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q2[/TD]
[TD="class: xl69"]MD[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl71"]Q3[/TD]
[TD="class: xl69"]GS[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q4[/TD]
[TD="class: xl69"]FR[/TD]
[TD="class: xl70"]5[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q1[/TD]
[TD="class: xl69"]LT[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q2[/TD]
[TD="class: xl69"]LL[/TD]
[TD="class: xl70"]5[/TD]
[/TR]
[TR]
[TD="class: xl71"]Q3[/TD]
[TD="class: xl69"]MB[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q4[/TD]
[TD="class: xl69"]PK[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q1[/TD]
[TD="class: xl69"]YJ[/TD]
[TD="class: xl70"]4[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q2[/TD]
[TD="class: xl69"]MB[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl71"]Q3[/TD]
[TD="class: xl69"]QK[/TD]
[TD="class: xl70"]2[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q4[/TD]
[TD="class: xl69"]GS[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q1[/TD]
[TD="class: xl69"]LL[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q2[/TD]
[TD="class: xl69"]FR[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl71"]Q3[/TD]
[TD="class: xl69"]HR[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q4[/TD]
[TD="class: xl69"]HB[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q1[/TD]
[TD="class: xl69"]MD[/TD]
[TD="class: xl70"]3[/TD]
[/TR]
[TR]
[TD="class: xl68"]Q2[/TD]
[TD="class: xl69"]MB[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl71"]Q3[/TD]
[TD="class: xl69"]MJ[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl72"]Q4[/TD]
[TD="class: xl73"]FR[/TD]
[TD="class: xl74"]2[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { font-family: Arial, sans-serif; text-align: center; border-color: windowtext currentcolor currentcolor windowtext; border-style: solid none none solid; border-width: 1pt medium medium 1pt; }.xl66 { font-family: Arial, sans-serif; text-align: center; border-color: windowtext currentcolor currentcolor; border-style: solid none none; border-width: 1pt medium medium; }.xl67 { font-family: Arial, sans-serif; text-align: center; border-color: windowtext windowtext currentcolor currentcolor; border-style: solid solid none none; border-width: 1pt 1pt medium medium; }.xl68 { text-align: center; border-color: currentcolor currentcolor currentcolor windowtext; border-style: none none none solid; border-width: medium medium medium 1pt; }.xl69 { font-family: Arial, sans-serif; text-align: center; }.xl70 { text-align: center; border-color: currentcolor windowtext currentcolor currentcolor; border-style: none solid none none; border-width: medium 1pt medium medium; }.xl71 { font-family: Arial, sans-serif; text-align: center; border-color: currentcolor currentcolor currentcolor windowtext; border-style: none none none solid; border-width: medium medium medium 1pt; }.xl72 { text-align: center; border-color: currentcolor currentcolor windowtext windowtext; border-style: none none solid solid; border-width: medium medium 1pt 1pt; }.xl73 { font-family: Arial, sans-serif; text-align: center; border-color: currentcolor currentcolor windowtext; border-style: none none solid; border-width: medium medium 1pt; }.xl74 { text-align: center; border-color: currentcolor windowtext windowtext currentcolor; border-style: none solid solid none; border-width: medium 1pt 1pt medium; }</style>
From the above data I would like to populate the following table:

[TABLE="width: 715"]
<tbody>[TR]
[TD="class: xl66, width: 87"][/TD]
[TD="class: xl67, width: 87"]Frequency[/TD]
[TD="class: xl68, width: 87"][/TD]
[TD="class: xl68, width: 87"][/TD]
[TD="class: xl68, width: 87"][/TD]
[TD="class: xl68, width: 87"][/TD]
[TD="class: xl67, width: 174, colspan: 2"]Average Score[/TD]
[TD="class: xl68, width: 87"][/TD]
[TD="class: xl68, width: 87"][/TD]
[TD="class: xl69, width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl70"][/TD]
[TD="class: xl71"]Q1[/TD]
[TD="class: xl71"]Q2[/TD]
[TD="class: xl71"]Q3[/TD]
[TD="class: xl71"]Q4[/TD]
[TD="class: xl71"]Total[/TD]
[TD="class: xl71"]Q1 Avg[/TD]
[TD="class: xl71"]Q2 Avg[/TD]
[TD="class: xl71"]Q3 Avg[/TD]
[TD="class: xl71"]Q4 Avg[/TD]
[TD="class: xl72"]Overall Avg[/TD]
[/TR]
[TR]
[TD="class: xl65"]FR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]GS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]HB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]HR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]LL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]LT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]MB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]MD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]MJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]PK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl65"]QK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl73"][/TD]
[/TR]
[TR]
[TD="class: xl74"]YJ[/TD]
[TD="class: xl75"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl76"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { font-family: Arial, sans-serif; text-align: center; border-color: currentcolor currentcolor currentcolor windowtext; border-style: none none none solid; border-width: medium medium medium 1pt; }.xl66 { border-color: windowtext currentcolor currentcolor windowtext; border-style: solid none none solid; border-width: 1pt medium medium 1pt; }.xl67 { font-family: Arial, sans-serif; border-color: windowtext currentcolor currentcolor; border-style: solid none none; border-width: 1pt medium medium; }.xl68 { border-color: windowtext currentcolor currentcolor; border-style: solid none none; border-width: 1pt medium medium; }.xl69 { border-color: windowtext windowtext currentcolor currentcolor; border-style: solid solid none none; border-width: 1pt 1pt medium medium; }.xl70 { border-color: currentcolor currentcolor currentcolor windowtext; border-style: none none none solid; border-width: medium medium medium 1pt; }.xl71 { font-family: Arial, sans-serif; }.xl72 { font-family: Arial, sans-serif; border-color: currentcolor windowtext currentcolor currentcolor; border-style: none solid none none; border-width: medium 1pt medium medium; }.xl73 { border-color: currentcolor windowtext currentcolor currentcolor; border-style: none solid none none; border-width: medium 1pt medium medium; }.xl74 { font-family: Arial, sans-serif; text-align: center; border-color: currentcolor currentcolor windowtext windowtext; border-style: none none solid solid; border-width: medium medium 1pt 1pt; }.xl75 { border-color: currentcolor currentcolor windowtext; border-style: none none solid; border-width: medium medium 1pt; }.xl76 { border-color: currentcolor windowtext windowtext currentcolor; border-style: none solid solid none; border-width: medium 1pt 1pt medium; }</style>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Welcome to Mr Excel Forum

Try something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[TD="bgcolor: #DCE6F1"]
J​
[/TD]
[TD="bgcolor: #DCE6F1"]
K​
[/TD]
[TD="bgcolor: #DCE6F1"]
L​
[/TD]
[TD="bgcolor: #DCE6F1"]
M​
[/TD]
[TD="bgcolor: #DCE6F1"]
N​
[/TD]
[TD="bgcolor: #DCE6F1"]
O​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Q#​
[/TD]
[TD]
Interviewer​
[/TD]
[TD]
Score​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Frequency​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Average Score​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Q1​
[/TD]
[TD]
YJ​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Q1​
[/TD]
[TD]
Q2​
[/TD]
[TD]
Q3​
[/TD]
[TD]
Q4​
[/TD]
[TD]
Total​
[/TD]
[TD]
Q1 Avg​
[/TD]
[TD]
Q2 Avg​
[/TD]
[TD]
Q3 Avg​
[/TD]
[TD]
Q4 Avg​
[/TD]
[TD]
Overall Avg​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Q2​
[/TD]
[TD]
MB​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD]
FR​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD]
3,00​
[/TD]
[TD][/TD]
[TD]
3,50​
[/TD]
[TD]
3,33​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Q3​
[/TD]
[TD]
HB​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD]
GS​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1,00​
[/TD]
[TD]
3,00​
[/TD]
[TD]
2,00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Q4​
[/TD]
[TD]
YJ​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
HB​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
3,00​
[/TD]
[TD]
1,00​
[/TD]
[TD]
2,00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Q1​
[/TD]
[TD]
LT​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD]
HR​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1,00​
[/TD]
[TD][/TD]
[TD]
1,00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Q2​
[/TD]
[TD]
PK​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD]
LL​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
2​
[/TD]
[TD]
1,00​
[/TD]
[TD]
5,00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
3,00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Q3​
[/TD]
[TD]
MJ​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD]
LT​
[/TD]
[TD]
2​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
2​
[/TD]
[TD]
3,50​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
3,50​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Q4​
[/TD]
[TD]
QK​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD]
MB​
[/TD]
[TD]
0​
[/TD]
[TD]
3​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD]
2,33​
[/TD]
[TD]
1,00​
[/TD]
[TD][/TD]
[TD]
2,00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
Q1​
[/TD]
[TD]
QK​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD]
MD​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
2​
[/TD]
[TD]
3,00​
[/TD]
[TD]
1,00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2,00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
Q2​
[/TD]
[TD]
MD​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
MJ​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
2​
[/TD]
[TD]
0​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2,00​
[/TD]
[TD][/TD]
[TD]
2,00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
Q3​
[/TD]
[TD]
GS​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
PK​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
3,00​
[/TD]
[TD][/TD]
[TD]
3,00​
[/TD]
[TD]
3,00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
Q4​
[/TD]
[TD]
FR​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD]
QK​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
3​
[/TD]
[TD]
4,00​
[/TD]
[TD][/TD]
[TD]
2,00​
[/TD]
[TD]
4,00​
[/TD]
[TD]
3,33​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
Q1​
[/TD]
[TD]
LT​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD]
YJ​
[/TD]
[TD]
2​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
3​
[/TD]
[TD]
3,00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2,00​
[/TD]
[TD]
2,67​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
Q2​
[/TD]
[TD]
LL​
[/TD]
[TD]
5​
[/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="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
Q3​
[/TD]
[TD]
MB​
[/TD]
[TD]
1​
[/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="bgcolor: #DCE6F1"]
17​
[/TD]
[TD]
Q4​
[/TD]
[TD]
PK​
[/TD]
[TD]
3​
[/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="bgcolor: #DCE6F1"]
18​
[/TD]
[TD]
Q1​
[/TD]
[TD]
YJ​
[/TD]
[TD]
4​
[/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="bgcolor: #DCE6F1"]
19​
[/TD]
[TD]
Q2​
[/TD]
[TD]
MB​
[/TD]
[TD]
3​
[/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="bgcolor: #DCE6F1"]
20​
[/TD]
[TD]
Q3​
[/TD]
[TD]
QK​
[/TD]
[TD]
2​
[/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="bgcolor: #DCE6F1"]
21​
[/TD]
[TD]
Q4​
[/TD]
[TD]
GS​
[/TD]
[TD]
3​
[/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="bgcolor: #DCE6F1"]
22​
[/TD]
[TD]
Q1​
[/TD]
[TD]
LL​
[/TD]
[TD]
1​
[/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="bgcolor: #DCE6F1"]
23​
[/TD]
[TD]
Q2​
[/TD]
[TD]
FR​
[/TD]
[TD]
3​
[/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="bgcolor: #DCE6F1"]
24​
[/TD]
[TD]
Q3​
[/TD]
[TD]
HR​
[/TD]
[TD]
1​
[/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="bgcolor: #DCE6F1"]
25​
[/TD]
[TD]
Q4​
[/TD]
[TD]
HB​
[/TD]
[TD]
1​
[/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="bgcolor: #DCE6F1"]
26​
[/TD]
[TD]
Q1​
[/TD]
[TD]
MD​
[/TD]
[TD]
3​
[/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="bgcolor: #DCE6F1"]
27​
[/TD]
[TD]
Q2​
[/TD]
[TD]
MB​
[/TD]
[TD]
1​
[/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="bgcolor: #DCE6F1"]
28​
[/TD]
[TD]
Q3​
[/TD]
[TD]
MJ​
[/TD]
[TD]
1​
[/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="bgcolor: #DCE6F1"]
29​
[/TD]
[TD]
Q4​
[/TD]
[TD]
FR​
[/TD]
[TD]
2​
[/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]
</tbody>[/TABLE]


Formula in F3 copied across till I3 and down
=COUNTIFS($A:$A,F$2,$B:$B,$E3)

Formula in J3 copied down
=SUM(F3:I3)

Formula in K3 copied across till N3 and down
=IF(F3>0,AVERAGEIFS($C:$C,$A:$A,LEFT(K$2,2),$B:$B,$E3),"")

Formula in O3 copied down
=AVERAGEIF(B:B,E3,C:C)

Hope this helps

M.
 
Upvote 0
It helps a tremendous amount! I am just not that savvy at this stuff yet, and am trying to coach myself up on it, but it is tough in a vacuum.

thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Adding a custom message.
Replies
1
Views
645
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
S
  • Question Question
Sending email from VBA (Excel) with Outlook
Replies
4
Views
2K
SaskiaHe
S

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

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