Excel Survey Help

  Taff™ 13:42 28 Jun 08
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 08

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 08

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 08

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.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5