Sum functoin formula with filtered row in excel-CodePudding

I have an Excel file that I want to apply the sum-formula to the filtered rows as well. Currently the total value does not change when I filter the rows. Could you please tell me how to correct this?

AM8

=SUMIF(INDIRECT("Ordersheet!$AL$" & $AO$5 & ":$AL$" & $AO$8);
    INDIRECT("Ordersheet!$AK$" & $AO$5 & ":$AK$" & $AO$8);
    "<>NO";INDIRECT("Ordersheet!$AI$" & $AO$5 & ":$AI$" & $AO$8);"<>STORNO")

AO8

=If(SUMPRODUCT(MAX(($A:$A<>"")*ROW($A:$A)))=7;8;
    SUMPRODUCT(MAX(($A:$A<>"")*ROW($A:$A))))

AO5=10

Excel-File

CodePudding user response：

To find the sum of filtered rows, you should use SUBTOTAL(109,...) instead of SUM. In below pics, you can see the difference when all rows are selected in Col versus when a few are.

Formula used is =SUBTOTAL(109, B2:B11) instead of =SUM(B2,B11)

Without filtered rows:

With filtered rows:

CodePudding user response：

Since you're using a SUMIFS() function (from the number and order of arguments) you can't use SUBTOTAL() directly, but you can do so, indirectly, by using a helper column.

The approach below involves you writing the formula

=SUBTOTAL(102,AL10)

in cell BA10 of your Ordersheet tab (assuming that column BA is currently empty), and then copying the formula down for all the rows in column AL that contain data. You can then modify your current SUMIFS() formula to the following

=SUMMEWENNS(INDIREKT("Ordersheet!$AL$" & $AO$5 & ":$AL$" & $AO$8);INDIREKT("Ordersheet!$AK$" & $AO$5 & ":$AK$" & $AO$8);"<>NO";INDIREKT("Ordersheet!$AI$" & $AO$5 & ":$AI$" & $AO$8);"<>STORNO";INDIREKT("Ordersheet!$BA$" & $AO$5 & ":$BA$" & $AO$8);1 )

effectively adding a condition that cells in column BA contain 1 (which they will only do if they are visible). (obviously you will need to change all the BA references to a different column if that column is currently populated with data)