Counting letter results to numbers in a different location

[caricc135]

I am creating a spreadsheet for another survey's results. The survey is ten questions with letters A - F as the answers. Each letter corresponds to a different result in a different location on the spreadsheet. What I want to do is count the corresponding letter selected. For example, if the letter A is selected three times, the number 3 needs to appear in the results cell.

This is my setup: There are 6 people who will be taking this survey ( 5 plus me). Each person is a row and each question is a column. Column A row 2-6, and 8 are the survey takers. Columns B-K are answer locations for the ten questions. Rows 11 -16 are the total results of each letter. (i.e., row 11 is A, row 12 is B, etc. through F.

Thank you in advance.

 

[Peter_SSs]

The best that I can make of your description and requirement is this

22 03 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Person	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10
2	Person 1	B	F	F	A	C	E	F	F	B	D
3	Person 2	D	E	F	B	B	E	C	F	D	A
4	Person 3	C	A	B	E	D	E	D	B	B	E
5	Person 4	B	B	C	E	E	B	F	D	A	D
6	Person 5	A	F	E	A	E	C	C	E	A	F
7
8	Person 6	D	D	C	F	D	A	A	A	D	B
9
10
11	A	1	1	0	2	0	1	1	1	2	1
12	B	2	1	1	1	1	1	0	1	2	1
13	C	1	0	2	0	1	1	2	0	0	0
14	D	2	1	0	0	2	0	1	1	2	2
15	E	0	1	1	2	2	3	0	1	0	1
16	F	0	2	2	1	0	0	2	2	0	1
Survey

Cell Formulas
Range	Formula
B11:K16	B11	=COUNTIF(B$2:B$8,$A11)

If that is not what you have & want then perhaps you can show us with the following?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[caricc135]

The best that I can make of your description and requirement is this

22 03 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Person	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10
2	Person 1	B	F	F	A	C	E	F	F	B	D
3	Person 2	D	E	F	B	B	E	C	F	D	A
4	Person 3	C	A	B	E	D	E	D	B	B	E
5	Person 4	B	B	C	E	E	B	F	D	A	D
6	Person 5	A	F	E	A	E	C	C	E	A	F
7
8	Person 6	D	D	C	F	D	A	A	A	D	B
9
10
11	A	1	1	0	2	0	1	1	1	2	1
12	B	2	1	1	1	1	1	0	1	2	1
13	C	1	0	2	0	1	1	2	0	0	0
14	D	2	1	0	0	2	0	1	1	2	2
15	E	0	1	1	2	2	3	0	1	0	1
16	F	0	2	2	1	0	0	2	2	0	1
Survey

Cell Formulas
Range	Formula
B11:K16	B11	=COUNTIF(B$2:B$8,$A11)

If that is not what you have & want then perhaps you can show us with the following?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

This is close, I need to show the results of Person 1 Row 2 in Cells B11-B16, Person 2 row 3 in Cells C11-16, etc.,

 

[Peter_SSs]

Like this?

22 03 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Person	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10
2	Person 1	B	F	F	A	C	E	F	F	B	D
3	Person 2	D	E	F	B	B	E	C	F	D	A
4	Person 3	C	A	B	E	D	E	D	B	B	E
5	Person 4	B	B	C	E	E	B	F	D	A	D
6	Person 5	A	F	E	A	E	C	C	E	A	F
7
8	Person 6	D	D	C	F	D	A	A	A	D	B
9
10		Person 1	Person 2	Person 3	Person 4	Person 5		Person 6
11	A	1	1	1	1	3		3
12	B	2	2	3	3	0		1
13	C	1	1	1	1	2		1
14	D	1	2	2	2	0		4
15	E	1	2	3	2	3		0
16	F	4	2	0	1	2		1
Survey (2)

Cell Formulas
Range	Formula
B10:H10	B10	=TRANSPOSE(A2:A8)&""
B11:H16	B11	=IF(B$10="","",COUNTIF(INDEX($B$2:$K$8,COLUMNS($B:B),0),$A11))
Dynamic array formulas.

If this is not what you want ..

If that is not what you have & want then perhaps you can show us with the following?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[caricc135]

Like this?

22 03 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Person	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10
2	Person 1	B	F	F	A	C	E	F	F	B	D
3	Person 2	D	E	F	B	B	E	C	F	D	A
4	Person 3	C	A	B	E	D	E	D	B	B	E
5	Person 4	B	B	C	E	E	B	F	D	A	D
6	Person 5	A	F	E	A	E	C	C	E	A	F
7
8	Person 6	D	D	C	F	D	A	A	A	D	B
9
10		Person 1	Person 2	Person 3	Person 4	Person 5		Person 6
11	A	1	1	1	1	3		3
12	B	2	2	3	3	0		1
13	C	1	1	1	1	2		1
14	D	1	2	2	2	0		4
15	E	1	2	3	2	3		0
16	F	4	2	0	1	2		1
Survey (2)

Cell Formulas
Range	Formula
B10:H10	B10	=TRANSPOSE(A2:A8)&""
B11:H16	B11	=IF(B$10="","",COUNTIF(INDEX($B$2:$K$8,COLUMNS($B:B),0),$A11))
Dynamic array formulas.

If this is not what you want ..

Thanks again Peter_SS. This worked.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.