Excel - Subtotal

Iron Contributor


I would like to use the function subtotal within an Excel sheet.

The function is

       =SUBTOTAL(103,'Page 1'!$A:$A)


At the ende it would be with an offsset



The rows are dynamic but the columns are fixed.

But Excel regards it as text and not as formula.

Is this a problem with the Excel Sheet



3 Replies


If the goal is essentially to create a dynamic range that omits hidden and filtered rows, you can use a 365 approach that avoids OFFSET:

    rng, $A$1:$G$50000,
    Col_A, TAKE(rng, , 1),
    Visible, LAMBDA(element, IF(SUBTOTAL(103, element) = 1, "", "omit")),
    Keep, MAP(Col_A, Visible),
    FILTER(rng, Keep <> "omit")
Thank you very much
I would like to use it in Power Automate
and the Formula is " =OFFSET(Tabelle1!A1;0;0;SUBTOTAL(103;Tabelle1!$A:$A);7)"
but I am getting an error. It might be because that cells are text an not numbers.


Do you generate Office Script for that?