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?

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.

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

Thanks for the support.

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

And starting with 2010 the AGGREGATE function

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post