Generating statistics based on filtered tables

[SNAPCymru]

So I'm trying to get a spreadsheet to generate statistics based on results from webforms.

I have set up various formulas to count different responses which all works very well. But I want those statistics to be dependant on the filtering of the table that feeds them.

For example. I need stats to be split along which county the report was made in. So I can filter my table by county, but the stats generated by referring to the table range remain the same even after filtered.

Is there a way to make the formula respond to the filtering of the table.

I have tried to use pivot tables to accomplish this and while they work very well for the simple numerical data, they are not suitable for analysing more complex responses, at least not so far as I've been able to manage.

Whatever help or suggestion you can provide will be much appreciated.

 

[venkat1926]

"subtotal" function have following values
[TABLE="width: 50, align: center"]
<tbody style="border-top-width: 1px; border-top-style: solid; border-color: rgb(204, 204, 204);">[TR]
[TD]1[/TD]
[TD]AVERAGE[/TD]
[/TR]
[TR="bgcolor: #F5F5F5"]
[TD]2[/TD]
[TD]COUNT[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]COUNTA[/TD]
[/TR]
[TR="bgcolor: #F5F5F5"]
[TD]4[/TD]
[TD]MAX[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]MIN[/TD]
[/TR]
[TR="bgcolor: #F5F5F5"]
[TD]6[/TD]
[TD]PRODUCT[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]STDEV[/TD]
[/TR]
[TR="bgcolor: #F5F5F5"]
[TD]8[/TD]
[TD]STDEVP[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]SUM[/TD]
[/TR]
[TR="bgcolor: #F5F5F5"]
[TD]10[/TD]
[TD]VAR[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]VAR[/TD]
[/TR]
</tbody>[/TABLE]

subtotal function works only on visible data. you can use this to get statistics of filtered data

 

[Excel Mechanic]

If there are not too many different types of formulas, I would suggest using the IF function. If you can send the file, I can probably help you with the formula construction.

 

[SNAPCymru]

Can't seem to get subtotals to work. I've tabled it and sorted it, but when I insert subtotals they don't sync with the table.

Found this on youtube which does a superb job, but was quite tricky and still has the odd draw back
https://www.youtube.com/watch?v=pCAXRLYr59E