Create Formula That Changes with Filter (Dynamic Range)

You are already using a pivot table. That is much more efficient than creating formulas. If you want to track several statistics at the same time, you may need to create several pivot tables next to each other. Instead of using filters, use slicers. A slicer (which is a filter with a nicer user interface) can be connected to several pivot tables and filter all of them at the same time.

If you find that the pivot table does not give you the correct result, consider refreshing the pivot table. The one in your file does need to be refreshed, because the data on the data sheet is not showing up.

Please see the attached file. I have used data up to row 1000 and put in some dummy values for different villages so we can see some results when we filter.

I have created a Report sheet and added three pivot tables to it. In each pivot table, drag the field you want to count to the row area and drag it again to the values area. This will then give you a count for each distinct value of the field.

If you want to see the different values arranged horizontally instead of vertically, then you drag the field to the Columns area instead of the Rows area. Excel will not allow overlapping pivot tables, so you may want to start a new sheet from scratch with your preferred layout.

I have added four slicers to the report sheet. Then I selected each pivot table in turn and clicked "Filter Connections" on the Pivot Table Analyze ribbon and ticked all boxes. This means that the slicers are now connected to ALL the pivot tables currently on the sheet and when I select a village, all pivot tables get filtered for that village.

The screenshot shows the report filtered for all married females in Bagh Killi. Attached is the file used to take the screenshot. Take a look and let me know how you get on.

2014-12-30_21-03-17.png
HDF Program Villages and progress-HLTH (1).xlsx

In the file I posted, the field "Nutritional Status" has been dragged into the Rows panel and also into the Values panel. This will create a row for each distinct text value in "Nutritional Status" and will count how often that value exists in the data, allowing for the filters.

In the file and the screenshot you can see that of the 20 married females in Bagh Killi, 13 are normal weight, 5 are obese and 2 underweight. Click in the pivot table and then look at the Pivot Table panel to see the details.

Text fields will automatically return a count.

Numeric fields may return a count, but can be changed to return a sum (or other aggregation) instead.

To connect a slicer to more than one pivot table, all pivot tables must be built on the same source data. Create the slicers for one pivot table. Then select another pivot table and click "Filter Connections" on the Pivottable Tools Analyze ribbon. The dialog will show all filters that are available for the selected pivot table and you can tick the ones that you want to connect to the current pivot table.

If you want to compare different villages, then I suggest you use one pivot table and drag the Village to the top of the Rows area. Then use the slicer to select the desired villages. You can use Shift-Click to select contiguous values and Ctrl-Click to select values that are not next to each other.

village in rows.png

To see the villages side by side, drag the village field to the columns area. Keep in mind that if your data set is very big, the pivot table will expand to many columns if you clear the slicer filter. Since pivot tables are not permitted to overlap you need to factor that in when you design your dashboard. With village in the columns, you should not have pivot tables to the right of that one, so you can select as many villages as you want without overlapping other data when the pivot table expands.

village in columns.png