SUBTOTAL, COUNTIFS, FILTERABLE DATA

[SFCChase]

Due to security protocols I am unable to download xl2bb so please bear with me. I received some help with some of this earlier, but haven't been able to finish out what I need.

I have a large data set that will change depending on the filtering of various columns.

Cell F2: =SUMPRODUCT(--(B5:B160<>""),--((E5:E160>=TODAY())+(E5:E160="N/A")>0),SUBTOTAL(103,OFFSET(B5,ROW(B5:B160)-ROW(B5),1,1))) Counts # of trainings that are not expired as of today.
Cell F3: =SUBTOTAL(103,B5:B160) Counts # of personnel assigned
CellF4: =IF(F2=0,"",F2/F3)

UNIT	LAST	FIRST		TRAINING 1	TODAY: 3/10/25
			# TRAINED		F2
			# REQUIRED		F3
			% TRAINED		F4
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:
F2=7
F3=9
F4=77.78%

If I filter Column A to only calculate "DCN" numbers I would like to result:
F2=1
F3=2
F4=50.00%

If I filter Column A to only calculate "MSU" numbers I would like to result:
F2=4
F3=5
F4=80.00%

Using the formulas in F2:F4 I am able to get the # of trained to change appropriately. The problem I'm having is that all my demographic info (columns A, B C) are fed from another tab using the formula =IF(DATA!A6="","",DATA!A6)

When there is no name in the Data tab (such as cell B8 and B9) I am left with a blank (which is what i want), but the SUBTOTAL formula in cell F3 doesn't change the #Required to reflect the filtered data (i.e. if I filter Unit to show DCN the # resulted is still 9 instead of changing to 2)

If there is anything I can do to be more clear, please let me know. Again I apologize for not being able to use xl2bb.....I wish I could!