Extract column total after filtering

[sofas]

Hello, I would like to extract the sum of column a after filtering. I tried to put the following formula, but to no avail =SUBTOTAL(109;a5:a1000)

Note that the values in column a are fetched from another sheet via the @Index formula

 

[Jeffrey Mahoney]

=SUBTOTAL(109,Range) is the correct method. 109 is the SUM of visible cells.

 

[Jeffrey Mahoney]

I tried it and the column values came out correct. The problem is when filtering the column

So you used SUM and filtered the range and got what you expected?

 

[Burrgogi]

=SUBTOTAL(109,Range) is the correct method. 109 is the SUM of visible cells.

I wasn't thinking clearly on that one, you're right.

 

[Fluff]

I suspect that the subtotal functions ignores cells that contain the aggregate function.
Are you still using xl2019 or do you have a newer version?

 

[sofas]

I suspect that the subtotal functions ignores cells that contain the aggregate function.
Are you still using xl2019 or do you have a newer version?

Currently using the 2021 version

 

[Fluff]

In that case remove all the formulas from C5 down & across & put this in C5 only

=FILTER('All Customers'!$C$4:$G$1000,'All Customers'!$E$4:$E$1000=REPLACE(CELL("filename",$A$4),1,FIND("]",CELL("filename",$A$4)),""))

 

[sofas]

In that case remove all the formulas from C5 down & across & put this in C5 only

=FILTER('All Customers'!$C$4:$G$1000,'All Customers'!$E$4:$E$1000=REPLACE(CELL("filename",$A$4),1,FIND("]",CELL("filename",$A$4)),""))

Very cool thank you

 

[Fluff]

Glad we could help & thanks for the feedback.