VLookup and Countif? Might be the wrong formula options for what I am trying to do... :)

[farmgirl1718]

Hi! I am so relieved to learn about the forum. I am a first time poster with my challenge below. I am appreciative for any and all help or guidance that you can provide.

I am trying to build a new report using database outputs. On the new report tab that I am creating, I created a dropdown list with survey name options, which I am trying to link to the information in the database output tab to show number of answers per option in the dropdown list.

The problem is, the countif and vlookup options are not correctly showing this information for one candidate or calculating based on the option referenced in the dropdown menu, because there is more than one option in the database output list. When using the countif or vlookup, I am having trouble with it calculating the match.

Please see my example below:

Database Output

Name, Answer 1
Candidate1, 4
Candidate1, 4
Candidate1, 2
Candidate1, 1
Candidate2, 3
Candidate2, 5
Candidate2, 3

New Report Tab
Summary, Answer 1 Criteria (1, 2, 3, 4, 5)
Candidate1, (1, 1, 0, 2, 0) (<=This is what I am trying to summarize from the database output per question per candidate.)


Thank you so much for all of your help!

 

[Akashwani]

Hi and welcome to MrExcel.

Is this what you mean?...

Sample data and example results...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Name	Answer 1		Name	1	2	3	4	5
2	Candidate1	4		Candidate1	1	1	0	2	0
3	Candidate1	4		Candidate2	0	0	2	0	1
4	Candidate1	2
5	Candidate1	1
6	Candidate2	3
7	Candidate2	5
8	Candidate2	3
9
Sheet1

The formula in E2 will work in versions of Excel 2007 and later.
The formula in E3 will work in all versions of Excel.

You will need to change the cell references to suit your layout and copy the formulas across and down/up depending on which one you chose.

I hope that helps.

Ak

 

[farmgirl1718]

Akashwani: Your recommendation worked very well! I cannot thank you enough for your help!!!! I really appreciate it!

 

[Akashwani]

Hi,

I'm pleased it worked for you and thanks for the feedback.

Ak