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

[AdMission]

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.

Q#	Interviewer	Score
Q1	YJ	2
Q2	MB	3
Q3	HB	3
Q4	YJ	2
Q1	LT	4
Q2	PK	3
Q3	MJ	3
Q4	QK	4
Q1	QK	4
Q2	MD	1
Q3	GS	1
Q4	FR	5
Q1	LT	3
Q2	LL	5
Q3	MB	1
Q4	PK	3
Q1	YJ	4
Q2	MB	3
Q3	QK	2
Q4	GS	3
Q1	LL	1
Q2	FR	3
Q3	HR	1
Q4	HB	1
Q1	MD	3
Q2	MB	1
Q3	MJ	1
Q4	FR	2

<tbody>
</tbody>

<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:

	Frequency					Average Score
	Q1	Q2	Q3	Q4	Total	Q1 Avg	Q2 Avg	Q3 Avg	Q4 Avg	Overall Avg
FR
GS
HB
HR
LL
LT
MB
MD
MJ
PK
QK
YJ

<tbody>
</tbody>

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

 

[Marcelo Branco]

Welcome to Mr Excel Forum

Try something like this

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

Q#​

Interviewer​

Score​

Frequency​

Average Score​

2​

Q1​

YJ​

2​

Q1​

Q2​

Q3​

Q4​

Total​

Q1 Avg​

Q2 Avg​

Q3 Avg​

Q4 Avg​

Overall Avg​

3​

Q2​

MB​

3​

FR​

0​

1​

0​

2​

3​

3,00​

3,50​

3,33​

4​

Q3​

HB​

3​

GS​

0​

0​

1​

1​

2​

1,00​

3,00​

2,00​

5​

Q4​

YJ​

2​

HB​

0​

0​

1​

1​

2​

3,00​

1,00​

2,00​

6​

Q1​

LT​

4​

HR​

0​

0​

1​

0​

1​

1,00​

1,00​

7​

Q2​

PK​

3​

LL​

1​

1​

0​

0​

2​

1,00​

5,00​

3,00​

8​

Q3​

MJ​

3​

LT​

2​

0​

0​

0​

2​

3,50​

3,50​

9​

Q4​

QK​

4​

MB​

0​

3​

1​

0​

4​

2,33​

1,00​

2,00​

10​

Q1​

QK​

4​

MD​

1​

1​

0​

0​

2​

3,00​

1,00​

2,00​

11​

Q2​

MD​

1​

MJ​

0​

0​

2​

0​

2​

2,00​

2,00​

12​

Q3​

GS​

1​

PK​

0​

1​

0​

1​

2​

3,00​

3,00​

3,00​

13​

Q4​

FR​

5​

QK​

1​

0​

1​

1​

3​

4,00​

2,00​

4,00​

3,33​

14​

Q1​

LT​

3​

YJ​

2​

0​

0​

1​

3​

3,00​

2,00​

2,67​

15​

Q2​

LL​

5​

16​

Q3​

MB​

1​

17​

Q4​

PK​

3​

18​

Q1​

YJ​

4​

19​

Q2​

MB​

3​

20​

Q3​

QK​

2​

21​

Q4​

GS​

3​

22​

Q1​

LL​

1​

23​

Q2​

FR​

3​

24​

Q3​

HR​

1​

25​

Q4​

HB​

1​

26​

Q1​

MD​

3​

27​

Q2​

MB​

1​

28​

Q3​

MJ​

1​

29​

Q4​

FR​

2​

<tbody>
</tbody>

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.

 

[AdMission]

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.

 

[Marcelo Branco]

You are welcome.

M.