0

I think this must be possible, but can't seem to figure it out.

I have the following data:

| responseid | Question       | Answer |
________________________________________
| 1          | Favorite Color | Red    |
| 1          | Favorite Food  | Pizza  |
| 1          | Favorite Sport | Soccer |
| 2          | Favorite Color | Red    |
| 2          | Favorite Food  | Pizza  |
| 2          | Favorite Sport | Darts  |
| 3          | Favorite Color | Red    |
| 3          | Favorite Food  | Tacos  |
| 3          | Favorite Sport | Soccer |

I want to know how many surveys had responses of both

Favorite Color == Red and Favorite Sport == Soccer.

In this case it would be 2 (id1 and id3)

thanks.

EDIT: Solution

What i was able to do however was pivot the data using powerquery features. Create a query from the table, select answer/question columns>transform tab>pivot>don't aggregate.

my table was then

response | color | food | sport 
1        | red   | pizza| soccer
2...
3...
wellmstein
  • 113
  • 5

3 Answers3

1

This uses a helper column with out the need for an array formula:

In the helper column use this formula:

=IF(AND(C2="Red",COUNTIFS(A:A,A2,C:C,"Soccer")),1,0)

Then sum the column.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Nice - This could work. I don't suppose this is possible without a helper column? The data is pulled from a connected source, so I was hoping to not have to manipulate the sheets on refresh. – wellmstein May 04 '17 at 15:15
  • I thought about this for a good 30 minutes before going this route, and could not think of any. If this is coming from an outside source you may want to talk to the provider about getting it in a tabular format instead. Then a simple COUNTIFS() would work. – Scott Craner May 04 '17 at 15:17
  • I'm going to give this one the check - as it works and is easy enough. my final solution using power query in edits above – wellmstein May 04 '17 at 17:08
0

try this 2 formulas: In a first step you need to enter the following array formula right next to the answer column (you have to confirm it with Ctrl+Shift+Enter instead of only pressing the Enter-key.):

=IF(A2=A1,"",SUM(($A$2:$A$10=A2)*(($B$2:$B$10="red")+($B$2:$B$10="soccer"))))

in the same column paste this normal formula at the end to count the surveys with the desired criteria:

=COUNTIF($C$2:$C$10,"=2")

Look at the following 2 images view with formulas: view with formulas

normal view: normal view

robrados
  • 144
  • 10
0

Assuming that the responses "Red" and "Soccer" can only ever occur once each per responseid:

=SUM(IF(FREQUENCY(IF(MMULT(COUNTIFS(C2:C10,{"Red","Soccer"},A2:A10,A2:A10),{1;1})=2,A2:A10),A2:A10),1))

Edit: in light of your comment, small adjustment:

=SUM(IF(FREQUENCY(IF(MMULT(COUNTIFS(B2:B10,{"Favorite Color","Favorite Sport"},C2:C10,{"Red","Soccer"},A2:A10,A2:A10),{1;1})=2,A2:A10),A2:A10),1))

Regards

XOR LX
  • 7,632
  • 1
  • 16
  • 15
  • Sadly no - the example was contrived but in some queries I have booleans or answers that can appear in multiple questions per survey. – wellmstein May 04 '17 at 17:08