yogi_Compute Stats Row By Row By Date And Tech Worker For Several Attributes

                                          Google Spreadsheet   Post  #1178
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 08, 2013
user Anna Project Tech Nick (https://productforums.google.com/forum/?hl=en#!category-topic/docs/spreadsheets/PIO43KKDubc)
counta autofill array
I am using a form to input data into a sheet so the first sheet i can't edit, so i have a second sheet for organizing the data. What i am trying to do is autofill the formulas as new data is entered into the "Form Responses" sheet. The first formula i am currently using is =CountA(Iferror(Filter('Form Responses'!C:C;'Form Responses'!C:C=ʺMikeʺ;'Form Responses'!L:L=A2))) 

From Form Responses Column C has names, column E to J has yes or no answers, column L has a date in mm/dd/yyyy format, Column A2 has the weekdays. 

Basically the formula totals how many times in a particular weekday that Mike shows.

The next set of formulas are based the same way

=CountA(Iferror(Filter('Form Responses'!E:E;'Form Responses'!E:E=ʺNoʺ;'Form Responses'!C:C=ʺMikeʺ;'Form Responses'!L:L=A2)))

=CountA(Iferror(Filter('Form Responses'!F:F;'Form Responses'!F:F=ʺNoʺ;'Form Responses'!C:C=ʺMikeʺ;'Form Responses'!L:L=A2)))

=CountA(Iferror(Filter('Form Responses'!G:G;'Form Responses'!G:G=ʺNoʺ;'Form Responses'!C:C=ʺMikeʺ;'Form Responses'!L:L=A2)))

=CountA(Iferror(Filter('Form Responses'!H:H;'Form Responses'!H:H=ʺNoʺ;'Form Responses'!C:C=ʺMikeʺ;'Form Responses'!L:L=A2)))

=CountA(Iferror(Filter('Form Responses'!I:I;'Form Responses'!I:I=ʺNoʺ;'Form Responses'!C:C=ʺMikeʺ;'Form Responses'!L:L=A2)))

Each one counts the total "No" responses for that line he has during that same day.

The only thing that has to change in the next row of formulas is A2 becomes A3, A4 etc

What i want to do is make an arrayformula based on the responses from the first sheet "Form Responses"

Any suggestions?

---

Also i don't just fill down because i have charts that pull the data and if i have a bunch of rows with 0's it skews the timeline chart.

---

This is the link to the document

https://docs.google.com/spreadsheet/ccc?key=0Ai8EJ_4lqLYcdEE2Y3cza3VYbjJLZlNBMUltNmNWcWc#gid=0

You can see the different sheets across the bottom, Form Responses cannot be modified, Charts pulls the data from each of the conversion sheets

-----------------------------------------------------------------------------------------------------------------------

let us have a look at the following