Subtotal in result row returns 0 in excel table

Question

I have an excel table ("lstTestSource") like this:

I have a second excel table ("lstTestResult"), which evaluates the first one:

There is a formula in each row in the second column ("lstTestResult"):

=SUMPRODUCT((lstTestSource[Color]=[@Color])*(SUBTOTAL(109; OFFSET(lstTestSource[[#Kopfzeilen];[Amount]]; ROW(lstTestSource[Amount])-ROW(lstTestSource[#Kopfzeilen]); 0))))

The formula is accurate; I can filter by "lstTestSource" and the results will display the correct values in the rows.

A result row appears at the conclusion of "lstTestResult," and no matter what filter is in effect, the result is always 0. There, too, I want to use the SUBTOTAL method.

The automatic calculation is turned on.

I looked into this issue on Google, but I was unable to locate any solutions. Can someone please help me with this?

Answer 1

Syntax

SUBTOTAL(function_num,ref1,[ref2],...)

The SUBTOTAL function syntax has the following arguments:

• Function_num     Required. The number 1-11 or 101-111 specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.

I hope this helps you.