# Excel 2007: Count value using criteria from AutoFiltered rows

#### Gmoney2

##### New Member
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

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### sbeatton

##### Active Member
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

##### New Member
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

##### Well-known Member
Pivottable/chart is your friend in cases like this!

Replies
0
Views
527
Replies
7
Views
587
Replies
0
Views
407
Replies
1
Views
232
Replies
0
Views
181

1,190,680
Messages
5,982,223
Members
439,769
Latest member
trungminh2802

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back