Trouble with Countifs / Hlookup formula

[Travisty]

Hello Excelforum!

I could really use some help. We used SurveyMonkey to conduct a survey of our students, but I'm finding it really hard to analyze the data in the format that Survey monkey spits out.

Basically, students were presented with a question (i.e. The Teacher outlined learning objectives) and could either choose "Strongly Disagree", "Moderately Disagree", "Moderately Agree", Or "Strongly Agree". I simply want to count the number of times a specific teacher received each response for reach question. In Theory this isn't difficult, but because of the layout of the raw data, I don't know how to design a formula to quickly do that.

It's a bit hard to explain over text, so I have attached a sample sheet to show what I mean. I would appreciate it if someone could look at the sheet and help me with the formula. I'm sure it's something to do with a Countifs and an Hlookup, but I can't put it together myself (Or am I on the wrong track?)

Thanks in advance! Really looking forward to the help

[Estevaoba]

Try in B3 and copy across and down:

Please Login or Register  to view this content.

Good luck!

[Travisty]

That seems to have done the trick! Thanks a bunch!

[Estevaoba]

You're welcome. Glad to help.
Thank you for the feedback and for the reputation added.
Have a blessed day!

[wk9128]

Provide another way to get the same solution
worksheet name : Summary
Cell B3 formula , Drag down and accross