Sunday, December 15, 2013

yogi_Count Incidents By CrossTabbing Antecedent and Behavior From Patterns Noted In Form Responses Sheet

                                          Google Spreadsheet   Post  #1437
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-14, 2013
question by Benjamin Yamato (http://productforums.google.com/forum/?zx=8wfymr6zg7vv#!category-topic/docs/spreadsheets/WYjVaSMgx5g)
Counting based on two criteria in a table.
I'm looking for a way to create a table of data based on two criteria generated from a google form.  I've attached the screenshot of the data I'm trying to use.  The user selects items from two checkbox lists, so there can be multiple entries in a single cell.  On a second worksheet (also attached), I'm trying to reference the data from the "Form Response" sheet to count incidents based on two criteria, which you can see in column/row headings.  

I'm looking for the formula to put in the cells on the second worksheet.  Based on googling/research, I've been trying to make this formula work:

=ARRAYFORMULA(SUM(('Form Responses'!$L:$L=$A2)*('Form Responses'!$U:$U=B$1)))

But it's just giving me a return value of "1", regardless of the data.

Thanks for any/all help!
Attachments (2)
Screen Shot 2013-12-12 at 8.47.09 AM.png
39 KB   View   Download
Screen Shot 2013-12-12 at 8.54.57 AM.png
37 KB   View   Download
------------------------------------