Pivot table question

[Tigerexcel]

I thought this would be a pretty simple process but alas... I would like to work out how may staff are under 50 per dept, I'd usually do this through a value filter which in this case is not available via the menu for Age (because it is number data type?). Is there a workaround or does the data require some preparation for this to work?

Book7
	A	B	C	D	E	F
1	Name	Dept	Age
2	Jim	HR	62
3	Harry	Sales	31		Row Labels	Count of Age
4	Bert	Admin	25		Admin	2
5	Tom	HR	33		HR	3
6	Helen	Sales	54		Sales	3
7	Kate	Admin	31		Grand Total	8
8	Noel	HR	49
9	Steve	Sales	32
Sheet1

 

[Tigerexcel]

Thanks Peter, your macro works well. I guess there's no non-VBA way to do this?

 

[Peter_SSs]

I guess there's no non-VBA way to do this?

Not if you want to continue using the Pivot Table.

If you abandon the Pivot Table for this and use Excel 365 with the new dynamic array functions then you just need these 2 formulas in E2 & F2 and the other results should 'spill' down.

Tigerexcel 1.xlsm
	A	B	C	D	E	F	G
1	Name	Dept	Age		Dept	Age less than:	35
2	Jim	HR	62		Admin	2
3	Harry	Sales	31		HR	1
4	Bert	Admin	25		Sales	2
5	Tom	HR	33
6	Helen	Sales	54
7	Kate	Admin	31
8	Noel	HR	49
9	Steve	Sales	32
10
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=SORT(UNIQUE(FILTER(B2:B20,B2:B20<>"")))
F2:F4	F2	=COUNTIFS(B2:B20,E2#,C2:C20,"<"&G$1)
Dynamic array formulas.

If you want/need it to work in both 365 and 2019 then copy these down as far as you might ever need.

Tigerexcel 1.xlsm
	A	B	C	D	E	F	G
1	Name	Dept	Age		Dept	Age less than:	35
2	Jim	HR	62		HR	1
3	Harry	Sales	31		Sales	2
4	Bert	Admin	25		Admin	2
5	Tom	HR	33
6	Helen	Sales	54
7	Kate	Admin	31
8	Noel	HR	49
9	Steve	Sales	32
10
Sheet3

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$20)/(ISNA(MATCH(B$2:B$20,E$1:E1,0))*(B$2:B$20<>"")),1)),"")
F2:F10	F2	=IF(E2="","",COUNTIFS(B$2:B$20,E2,C$2:C$20,"<"&G$1))

 

[Tigerexcel]

Thanks Peter I like that formula.

I actually like using PTs, I was wondering whether there was a method within the PT settings that would allow you to collapse the detail and just show the totals as the number of columns gets large when you add the field to the column label.

 

[Peter_SSs]

Nothing that would work very well for what you are trying to do as far as I know (though Pivot Tables are not a particular strength of mine)

 

[Tigerexcel]

Nothing that would work very well for what you are trying to do as far as I know (though Pivot Tables are not a particular strength of mine)

Thanks Peter.