Auto Sum calculate the values of rows that are hidden ...

[LearnerExcel]

I have the first row with auto sum function to calculate the (Total Invoice Value) which is in column "H".



There are hundreds of invoices with details. So, when I want to filter any one invoice, say, Invoice No. 1550 (Which has 10 rows with 10 item details), the Total Invoice Value is showing the sum of whole column not of just these 10 rows.

How to resolve this issue?

[Alsadius]

What I usually do here is make a dummy column that's set to 1 if you want to count that row and 0 if you don't. Then just SUMPRODUCT the price with that dummy column.

For example, let's say column A is invoice number, B is item ordered, and C is price. You also have cell D1 with the invoice number you're filtering for. You'd add column E, and in E1 you'd type =IF(A1=$D$1, 1, 0), and copy that all the way down column E. Then the sum of that particular invoice is =SUMPRODUCT($A$1:$A$9999, $E$1:$E$9999). You're multiplying all prices on different invoices by zero(ignoring them) and on the desired invoice by 1(keeping them), and adding up the total.

[NBVC]

use the SUBTOTAL() function. It sums the filtered values.

[LearnerExcel]

Thanks for the support.

 
=Subtotal(function number, data range)

The function number to sum filtered data is 9.
The formula would be defined as follows:

=Subtotal(9,A1:A10)

[Pecoflyer]

And starting with 2010 the AGGREGATE function