Table Filter average not refreshing

Question

Hello

I regularly work on a spreadsheet where I convert the data into Table and use the 'Last Row' for calculation. Then I can filter 2 of the columns to count averages of those that have data in them and those that don't against all data.

When I create these tables, and filter, sometimes the result recalculates and other times it doesn't. Figured out that it all depends on a number in the formula

	A	B	C	D	E
1	Name	Gend	Y/N	En	Ma
2	A	F		-2	-1
3	B	M	Y	0	0
4	C	M		0	3
5	D	F	N	-1	-1
6	4	4	2	-0.750	0.250

Example: When I filter 'M' in the Gender column, the total which is =counta(...  becomes 2 and the totals in D6 and E6 is supposed to recalculate to just count the Filtered rows but only if the formula is =SUBTOTAL(101,[En])

Or if you want a =COUNTA(....  you have to put =SUBTOTAL(103,[En])

Is there somewhere in Excel that tells you what number corresponds to what formula?

Thank you

J

Answer 1

Hi,

It's all in Excel help under SUBTOTAL.

Just to clarify. If your using subtotal on a filtered range either in a table or by applying a filter then subtotal 3 does the same as 103 because as both ignore hidden rows in this instance and return only visible cells.

The only time 3 and 103 (or all other options) behave differently is if the rows are hidden by actually hiding a row using Format|Hide rows.

Function_num <br>(includes hidden values)	Function_num <br>(ignores hidden values)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR

Answer 2

Wow!! did not know that. So you can actually do this without turning the data into a table?

This is FANTASTIC!!

I love excel!!

Thank you Mike. (I love this forum!)