When working with your exports, you may want to know "how can I tell who selected this response and that one?" Data for each response option is stored in separate columns, so we need to create a new one which will tell us a simple yes or no as to whether our requirements have been met.

We will be working in the Master Data tab, which you can read more about here.

The key to understanding here is that a 1 in a column we are interested in is a positive response, and a 0 means that answer was not selected by that respondent. Each of the examples below will will make use of summing the responses and applying AND or OR logic depending on what we want to achieve.

Final note: these will work for responses within the same question, or different questions!

We will use this question for our example.

Aim: who selected 'Clubs' and 'Bars/ cafés'?

First things first - check your data map to see which question and response codes you want to look at. We'll then be focused on the corresponding columns in Master Data. In our example, we will be looking at 'Clubs' and 'Bars/ Cafés' - so we're interested in response codes Q3_R2 and Q3_R3.

Below are the columns in Master Data. We have added a new column (to the right of demographics) where we will input our formula. The columns we are interested in are highlighted.

If a respondent has selected 'Clubs' then there will be a 1 in column AJ; the same applies to 'Bars/ cafés' and column AK. Therefore if both options are selected, we would expect a total of 2.

For this example, the formula in the new column is SUM(AJ20:AK20)>=2.

The output of this will either be TRUE or FALSE because we are saying that one thing (the sum) is equal to another (2).

This method can be expanded to look at any number of answers, either within the same question, or from different questions, you just need the response codes to find the relevant columns.
​

Goal: who only selected 'Home'?

Here's a reminder of the data map.

This time, we want:

We can ignore skips, as if skip is 1, it is impossible to answer the question, so there will be a 0 in all other columns for this question.

Here, we will use the AND formula which returns TRUE if all conditions are met, and FALSE if not.

This time, the formula in the new column is =AND(AI16=1,SUM(AJ16:AM16)=0).

The AND formula checks two things:

When both are true, TRUE is shown.
​

Goal: who selected "Home" or "a friend's place" (but not necessarily both)?

Here's another reminder of the data map.

This time, we want:

We can use SUM again to check the total of both responses, but instead of using equal to, we want to check whether the total is greater than or equal to 1.

Into the new column, we enter this formula: =SUM(AI63,AL63)>=1

If neither option is selected, the total will be 0 and show FALSE.

If only one option is selected, the total will be 1 and show TRUE.

If both options are selected, the total will be 2 and also show TRUE.
​

If you're doing this for the first time, be sure to check these areas if things don't look quite right:

1. Are you checking for the correct totals?

If you want one thing AND another, your totals should be the same as the number of answers you are checking. For example, for 2 answers the total should be 2; for 3 answers the total should be 3 and so on.

If it's one thing OR another, you will want a minimum total of 1.

2. Are you using SUM correctly?

Make sure you know the difference between =SUM(A2,D2) and SUM(A2:D2).

Using a comma will just sum the two selected cells, but a colon will sum them as well as every column or row in between.