SUBTOTAL with COUNTIFS

[SFCChase]

Is it possible to combine SUBTOTAL and COUNTIFS?

I have a data set that will change depending on filtering various columns. Currently I'm using the following formulas:

Cell E2: =COUNTIFS($B$5:$B$160, "<>",E5:E160,">="&TODAY())+COUNTIFS($B$5:$B$160, "<>",E5:E160,"N/A") Counts # of trainings that are not expired as of today
Cell E3: =COUNTIF($B$5:$B$160,"") Counts # of personnel assigned
Cell E4: =IF(E2=0,"",E2/E3) Calculates overall completion percentage (# of non-expired trainings/# of assigned personnel)

I would like the value of Cell H8 to change based on the # of assigned personnel shown when I filter Column A.

I have figured out a SUBTOTAL formula that will work for Cell E3 (=SUBTOTAL(3,$B$5:$B$160), but I can't figure out a SUBTOTAL for Cell E2 and therefore end up with % over 100% when I filter.

				TRAINING 1
			# TRAINED
			# REQUIRED
UNIT	LAST	FIRST	% TRAINED
DCN	ADAMS	AARON		5/20/25
DCN	JONES	JOHN		2/1/25
IPSL	MICHAELS	MICHAEL		5/20/25
IPSL	JULES	JULIE		N/A
IPSL
MSU	SNUFFY	JOE		3/25/25
MSU	BAKER	BARNEY		3/24/25
MSU	LEWIS	LOU		2/1/25
MSU	JUAREZ	JUAN		N/A
MSU	SMITH	SAM		3/21/25

Given the data above....if I leave unfiltered:
E2=7
E3=9
E4=77.78%

If I filter Column A to only calculate "DCN" numbers I would like to result:
E2=1
E3=2
E4=50.00%

If I filter Column A to only calculate "MSU" numbers I would like to result:
E2=4
E3=5
E4=80.00%

Any suggestions would be appreciated!

 

[Peter_SSs]

Cell E3: =COUNTIF($B$5:$B$160,"") Counts # of personnel assigned

I'm struggling somewhat with that. How does counting blanks equate to the number of personnel assigned?

Are you still using Excel 2016?

 

[Peter_SSs]

I'm struggling somewhat with that. How does counting blanks equate to the number of personnel assigned?

So, I am assuming that E3 should count the names in column B & have adjusted the E3 formula to do that.
For the filtering I have suggested some formulas in F2:F4. If the only choices in column E are Date, "N/A" or blank (& the col E values are not the result of formulas) then the F2 formula could be simplified to

=SUMPRODUCT(--(B6:B161<>""),--(E6:E161>=TODAY()),SUBTOTAL(103,OFFSET(B6,ROW(B6:B161)-ROW(B6),1,1)))

25 03 08.xlsm
	A	B	C	D	E	F
1					TRAINING 1
2				# TRAINED	7	7
3				# REQUIRED	9	9
4	UNIT	LAST	FIRST	% TRAINED	77.78%	77.78%
5	DCN	ADAMS	AARON		20/05/2025
6	DCN	JONES	JOHN		1/02/2025
7	IPSL	MICHAELS	MICHAEL		20/05/2025
8	IPSL	JULES	JULIE		N/A
9	IPSL
10	MSU	SNUFFY	JOE		25/03/2025
11	MSU	BAKER	BARNEY		24/03/2025
12	MSU	LEWIS	LOU		1/02/2025
13	MSU	JUAREZ	JUAN		N/A
14	MSU	SMITH	SAM		21/03/2025
15
ST

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($B$5:$B$160, "<>",E5:E160,">="&TODAY())+COUNTIFS($B$5:$B$160, "<>",E5:E160,"N/A")
F2	F2	=SUMPRODUCT(--(B5:B160<>""),--((E5:E160>=TODAY())+(E5:E160="N/A")>0),SUBTOTAL(103,OFFSET(B5,ROW(B5:B160)-ROW(B5),1,1)))
E3	E3	=COUNTA($B$5:$B$160)
F3	F3	=SUBTOTAL(103,B5:B160)
E4:F4	E4	=IF(E2=0,"",E2/E3)

After filtering ..

25 03 08.xlsm
	A	B	C	D	E	F
1					TRAINING 1
2				# TRAINED	7	1
3				# REQUIRED	9	2
4	UNIT	LAST	FIRST	% TRAINED	77.78%	50.00%
5	DCN	ADAMS	AARON		20/05/2025
6	DCN	JONES	JOHN		1/02/2025
161
ST

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($B$5:$B$160, "<>",E5:E160,">="&TODAY())+COUNTIFS($B$5:$B$160, "<>",E5:E160,"N/A")
F2	F2	=SUMPRODUCT(--(B5:B160<>""),--((E5:E160>=TODAY())+(E5:E160="N/A")>0),SUBTOTAL(103,OFFSET(B5,ROW(B5:B160)-ROW(B5),1,1)))
E3	E3	=COUNTA($B$5:$B$160)
F3	F3	=SUBTOTAL(103,B5:B160)
E4:F4	E4	=IF(E2=0,"",E2/E3)