Excel Survey Help

  Taff™ 13:42 28 Jun 2008
Locked

I have sent a questionnaire out to members of our society regarding meetings and I`m trying to analyse some of the results. Two of the questions relate to preferred venues and I want to know how I can identify those respondents who have answered a question with the response "Yes" who live in Birmingham.

I have in Column C respondents Location. In Column P they have answered "Yes" to the question "would you prefer all meetings to be held in Birmingham" - I want to be able to identify the number of people who live in Birmingham that obviously answered yes to the question.

  VoG II 13:55 28 Jun 2008

Try

=SUMPRODUCT(--(C1:C1000="Birmingham"),--(P1:P1000="Yes"))

You can adjust the row references if needed but you must reference exactly the same number of cells in each column. You cannot use whole column references like C:C except in Excel 2007.

Note: -- is two consecutive minus signs.

  Taff™ 14:22 28 Jun 2008

Absolutely spot on VoG™ - Thank You again. I`ll leave this open if I may. There may be another similar question I need to filter.

  daba 22:29 28 Jun 2008

You might like to use data validation for your Yes/No response to prevent all the possibilities of replying in the affirmative - "Yes", "yes", "Y", "y". The validation rule can include a drop-down selection if you wish.

But the location could be trickier : you could put a (hidden) column in like =PROPER(C1) to tidy up responses beginning with lowercase, then point VoGs formula to the new column.

I'm certain macro-code is possible (and preferable) to trap all input and "Proper" it automatically into the cell to ensure all your formulas work as expected.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Microsoft Surface Book 2 hands-on review – bigger and 5x faster

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?