Excel 2007: Count value using criteria from AutoFiltered rows

[Gmoney2]

Spent half a day on this. Hoping half an hour on this board can help solve my problem.

I have a range of data (-2 to 2) gathered from a survey which I am using to create charts for my 5 potential responses in a 100% stacked column
chart. This currently works by using the COUNTIF function for the total range but I would like for the chart to recalculate depending on the filtered data of the demographic information I captured (e.g.: data filtered to only show male responses would create a new chart with only the respondent data for males)

Can this be done? I tried to use =SUMPRODUCT((A2:A10000="x")*SUBTOTAL(3,OFFSET(A2,ROW(A2:A10000)-ROW(A2),0)))
which others had posted as a solution to this issue but I think it only works when the data value is text and not numerical, as in my case.

Thoughts?

G

 

[sbeatton]

Without seeing an example of you data it is hard to tell, but have you thought of using a Pivottable and a Pivot Chart. This will instantly update when you reformat the table.

 

[Gmoney2]

It's a thought but frankly, too much has been built around the existing structure and I was hoping to just adjust the existing formulas to capture what I need.

As for sample data, consider a range of 50 columns capturing responses to individual survey questions and about 600+ rows representing unique responses to the survey. The first 5 columns represent demographic type information: age, sex, etc. The remaining 45 columns are the answers to the strongly agree (+2) to strongly disagree (-2) survey.

I would like to be able to filter by the demographic information and be able to determine what it does to the count and percentage of responses by the filtered data results (as shown in the 100% stacked column chart)

Hope this provides more clarity.

 

[dafan]

Pivottable/chart is your friend in cases like this!