apply formula only to data shown after filters applied

[misstoffeepenny]

Hi, I am trying to develop a matrix that when I apply a series of different filters it will generate counts and calculations based on the data visible. there are over 40 skills to calculate 5 different outputs for.

I have shown a small selection in the mini sheet below. I want to be able to filter by department, site & status and then calculate the values in a6:a10 for each of the labels in row 5. I know this is a complicated ask but any help is much appreciated. This is an ever changing document & skills & staff members will be added constantly so I need the solution to take that into account. The sheet is also linked to other sheets which help to generate this data.

training matrix draft working.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
5	Staff member	Job Role	Site	Status	Company Induction		Risk Assessments		Site/Role Specific RAs		HR 1		HR 2		HR 3		HR 4		HR 5
6	Count of Competent Staff per Skill (level 3+)				2		2		2		1		1		1		1		1
7	Required Count of Competent Staff per Skill (level 3+)				3		3		3		3		3		3		3		3
8	Competency Gap				1		1		1		2		2		2		2		2
9	Current Skill Competency %				####		####		####		####		####		####		####		####
10	Target Skill Competency %				####		####		####		####		####		####		####		####
11	Staff Member	Department	Site	Status	C	T	C	T	C	T	C	T	C	T	C	T	C	T	C	T
12	abc	Administration	Dunloy	Employee	3	3	4	3	3	3	3	3	3	3	3	3	3	3	3	3
13	def	HR	Coleraine U5	Employee	3	4	3	3	3	3	1	4	1	4	1	4	1	4	1	4
14	ghi	Fabricator	Garvagh SS	Employee	1	3	2	3	2	3		3		3		3		3		3
15
16
17
18
Matrix

Cell Formulas
Range	Formula
E5	E5	=IF(Departments!E5<>"",Departments!E5,"")
G5	G5	=IF(Departments!F5<>"",Departments!F5,"")
I5	I5	=IF(Departments!G5<>"",Departments!G5,"")
K5	K5	=IF(Departments!H5<>"",Departments!H5,"")
M5	M5	=IF(Departments!I5<>"",Departments!I5,"")
O5	O5	=IF(Departments!J5<>"",Departments!J5,"")
Q5	Q5	=IF(Departments!K5<>"",Departments!K5,"")
S5	S5	=IF(Departments!L5<>"",Departments!L5,"")
E6,G6,I6,K6,M6,O6,Q6,S6	E6	=COUNTIF(E12:E339,">2")
E7,G7,I7,K7,M7,O7,Q7,S7	E7	=COUNTIF(F12:F339,">2")
E8,G8,I8,K8,M8,O8,Q8,S8	E8	=E7-E6
E9,G9,I9,K9,M9,O9,Q9,S9	E9	=((SUM(E12:E339)/COUNT(E12:E339))/4)
E10,G10,I10,K10,M10,O10,Q10,S10	E10	=((SUM(F13:F340)/COUNT(F13:F340))/4)
F12:F14	F12	=VLOOKUP(B12,dept,4,FALSE)
H12:H14	H12	=VLOOKUP(B12,dept,5,FALSE)
J12:J14	J12	=VLOOKUP(B12,dept,6,FALSE)
L12:L14	L12	=VLOOKUP(B12,dept,7,FALSE)
N12:N14	N12	=VLOOKUP(B12,dept,8,FALSE)
P12:P14	P12	=VLOOKUP(B12,dept,9,FALSE)
R12:R14	R12	=VLOOKUP(B12,dept,10,FALSE)
T12:T14	T12	=VLOOKUP(B12,dept,11,FALSE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C11:D11	Cell Value	contains "PPC"	text	NO
C11:D11	Cell Value	="Garvagh SS"	text	NO
C11:D11	Cell Value	contains "Garvagh TBF"	text	NO
C11:D11	Cell Value	="Magherafelt"	text	NO
C11:D11	Cell Value	contains "Coleraine U11"	text	NO
C11:D11	Cell Value	contains "Coleraine U5"	text	NO
C11:D11	Cell Value	contains "Dunloy"	text	NO
C3:D3,C12:D1048576,C5:D5	Cell Value	contains "PPC"	text	NO
C3:D3,C12:D1048576,C5:D5	Cell Value	="Garvagh SS"	text	NO
C3:D3,C12:D1048576,C5:D5	Cell Value	contains "Garvagh TBF"	text	NO
C3:D3,C12:D1048576,C5:D5	Cell Value	="Magherafelt"	text	NO
C3:D3,C12:D1048576,C5:D5	Cell Value	contains "Coleraine U11"	text	NO
C3:D3,C12:D1048576,C5:D5	Cell Value	contains "Coleraine U5"	text	NO
C3:D3,C12:D1048576,C5:D5	Cell Value	contains "Dunloy"	text	NO
E12:CH339	Cell Value	="X"	text	NO
E12:CH339	Cell Value	=4	text	NO
E12:CH339	Cell Value	=3	text	NO
E12:CH339	Cell Value	=2	text	NO
E12:CH339	Cell Value	=1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B12:B339	List	=Departments!$B$6:$B$32
C12:C259	List	=dropdown!$B$3:$B$17
D12:D339	List	=dropdown!$H$3:$H$5

 

[duggie33]

Hi misstoffeepenny,

See if the SUBTOTAL function would meet your needs. It works with many functions (COUNT, COUNTA, SUM, AVERAGE, MIN, MAX, etc.) and, for me, summed only visible rows in a Table.

Doug

 

[misstoffeepenny]

Hi misstoffeepenny,

See if the SUBTOTAL function would meet your needs. It works with many functions (COUNT, COUNTA, SUM, AVERAGE, MIN, MAX, etc.) and, for me, summed only visible rows in a Table.

Doug

unfortunately this only works if I put the data into a table, which I cannot do as it's all linked to other sheets within the workbook

 

[misstoffeepenny]

so I have found this formula online that appears to work except it doesn't give me the correct value which should be 2 not 0
can anyone help me figure out the part I have wrong?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E12:E339,ROW(E12:E339)-ROW(E12),0,1)),--(E12:E339=">2"))

 

[Fluff]

How about with a helper column using

=SUBTOTAL(103,A12)

and then use

=COUNTIFS(E12:E339,">2",U12:U339,1)

Change col U to the column with the subtotal formula

 

[misstoffeepenny]

got it sorted thanks
just had to amend the formula to:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E12:E339,ROW(E12:E339)-ROW(E12),0,1)),--(E12:E339>2))

 

[Fluff]

Did you try what I suggested? It's more efficient & non volatile.