Monday, June 3, 2013

yogi_Pull Info Into Target Rows From Data In Sequentially Placed Columns In Source

                                          Google Spreadsheet   Post  #1227
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 03, 2013
user JoaoF and question by Stephanie Parry (http://productforums.google.com/forum/?zx=v9avjn87gmxc#!mydiscussions/docs/yY9Qksntatk)

 I am trying to combine an filter and index inside an array formula and having trouble with syntax

I want to filter data by month (month is in column AO), and then do a countif using index/match per survey question and type of answer.  I'm using this formula to extract the data I want without filtering by month (all data):

=countif(INDEX(Data!$A$1:$AQ$101, 0, Match($A3,Data!$1:$1, 0)), "Yes")

I have another page where I am reporting by month and using this formula:

=COUNTA(iferror(filter(Data!$AQ:$AQ; Data!$AQ:$AQ =$I$3; Data!N:N = "Yes")))

The problem is that I have to pull information for each survey question by column and I can't drag down and automatically change cell reference. It's easier to use index match like in the first formula. So, I'd like to combine the filtering with the countif idex/match. This is getting a little complicated for my knowledge level.

I hope this makes sense...
---
I am creating a summary of responses to survey questions by month.  Data is on the first sheet, second sheet has the year-to-date report with index/match formulas in cells b3:d26 to count responses by questions and answer choice.  On the 3rd tab I wanted a similar report but filtered by month (using drop down menu in cell I3).  I have a formula that works fine (see cells b3:b5) but I don't want to have to change the cell reference when I copy the formula down (cell references are columns on the data sheet and as far as I know those references will not automatically fill in as series when I drag down).  I know there must be a better way to do this (maybe by adding index/match to the existing formula instead of putting column reference?)
--------------------------------------------------------------------------------------------------------------------