How to add SUBTOTAL to SUMPRODUCT with division formular in Excel?

Question

Below are some raw data with related formular:

Col A1, A2 & A3: 4, 6 & 12 
Col B1, B2 & B3: 100, 150 & 120 
Formular: = SUMPRODUCT(B1:B3/A1:A3)

I tried to add SUBTOTAL function in front of the above formular when filtering use, but it doesn’t work......Can you help on this?

The combined SUMPRODUCT DIVISION plus SUBTOTAL formular is expected when filtering use.Thx!

Answer 1

Sum Up Divisions in a Filtered List Using AGGREGATE

Formula

=LET(n,B5:B7,d,A5:A7,
    v,BYROW(d,LAMBDA(r,AGGREGATE(2,5,r))),
SUM(n/d*v))

The 1st BYROW parameter can be either column. I opted for the divisor having in mind division by zero.

Variables

n - Numerator or Dividend
d - Denominator or Divisor
v - Visibility (1 or 0)

Legacy Excel

=SUMPRODUCT(SUBTOTAL(109,OFFSET(B5,ROW(B5:B7)-ROW(B5),))/A5:A7)