Excel lookup

[funkylad20]

I have a questionnaire with 17 questions. The answer for each question is one of the following; 'very satisfied', 'satisfied', 'neither satisfied nor dissatisfied', 'dissatisfied', 'very dissatisfied' and 'did not use'.

I have labelled each question numerically, 1-17.

The results are fed through with each question on the left column (A) and a 1 or a 0 in columns B - H, depending on where the questionnaire candidate put their response.

I want to somehow collate all of the responses to form pie charts for each question individually.

Any suggestions? This is not a one off exercise, so need to establish a set area on a different tab to feed through the results at the press of a refresh button!

[Maydup]

Just to clarify, you say you have 1 or 0 in the columns depending upon the replies. When the next reply comes in does the 1 become 2 and so on?

I'm imagining a row per question with the final reults along eacg row readng like this for example.

26 very satisfied, 20 satisfied, 8 niether, 1 dissatisfied, 0 very dis, 7 did not used.

If so, hightlight that row and click on the graph wizard. Follow the wizard through (ask again if ytou need help) and save the graph as a new tab called Q1 graph.

[funkylad20]

The 1 entry will be the cross in the box, or the yes, the one agreed strongest statement. The 0 will just be the others. Each question can only have one response, so one '1' per question.

I need to use countif / if / sumif I think, but think it needs to be an amalgamation of two.

In normal terms, I want to say 'lookup and see if a row refers to question '1', then feed the results of that row from column'.

[funkylad20]

Sussed it!

=SUMIF(Data!$B:$B,"1",Data!D:D)

[Old_Geezer]

I thought if you highlighted the data in your table you had a menu option for a 'wizard' that would guide you through making charts & graphs of whatever desired sort.

[Old_Geezer]

Tsk 2 replies that weren't there when I started typing !

[Old_Geezer]

Tsk tsk tsk I mean 3 posts that weren't there when I started reading !

[Maydup]

Pleased you've sussed it. For future reference, if you want to count rather than add, just replace the word Sumif with Count. And countif to only count certain cells. eg to count the cells which contain and x use.............
=COUNTIF(B2:B21,"x")