Array calculation - Survey results

[WorthJ]

Hello,

I've got a 6 questions in a survey asking in different ways whether someone feels better after using our service.

People can answer Strongly agree, Agree, Neutral, Disagree or Strongly disagree to each of the 6 questions.

I want to do a calculation to see how many people answered "Strongly agree" or "Agree" to every single question.

I have 163 responses
Each of the 6 questions are in different columns. The data is in A2:F165

For a single response I thought I could do

=if(
(countif(A2:F2, "Strongly agree")
+
countif(A2:F2, "Agree"))
=6,
True)

I want to count the number of "True" responses across all of the data. I feel an array formula is the way forward but I don't understand how to use them.

Thanks!

 

[steve the fish]

See if this works:

=SUM(--(MMULT(--((A2:F165="Strongly Agree")+(A2:F165="Agree")),{1;1;1;1;1;1})=6))

 

[WorthJ]

Yep that works, thanks! Now I just need to work out how it works

 

[BrianGGG]

For:
=SUM(--(MMULT(--((A2:F165="Strongly Agree")+(A2:F165="Agree")),{1;1;1;1;1;1})=6))

Starting with the inside functions (e.g. = Strongly Agree), this is a simple Boolean check on the entire array that will create a bunch of TRUE/FALSE based on each value.
The "--" at the start converts the TRUE/FALSE into 1 or zero. By adding the 1 or 0 for each given cell, there will be a one in the cell if it contains Strongly or Agree. It will contain a zero otherwise.

The matrix multiplication (MMULT) is a fancy way of creating a total for each row. At the end of this function, each row will contain a number between 1 and 6 based on the booleans.
Now the final boolean on the outside is looking for rows where the SUM is 6. These are the ones where a Strongly or Agree appear in every column of that row.
By summing the number of rows that add up to 6, this gets you the answer you are looking for...


BrianGGG

 

[WorthJ]

Thanks BrianGGG,

That makes sense, though I'd probably have a bit of trouble creating it from scratch myself. I'll do some reading around matrices.