Trying to get SUMPRODUCT to be dynamic with a table

My current formulas is as follows:

Please Login or Register  to view this content.

The table "Data_New" has other columns available such as "customer," "year," etc. I have some slicers on this table and would like the above formula to be dynamic when I select a customer and the table changes. To note, customer is not currently a part of the current formula and I do not want it to be.

EDIT: I think I need to add SUBTOTAL and OFFSET but this didn't work:

Please Login or Register  to view this content.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

This seems like it will theoretically will work, but I tried adding a column called "visible" and used that subtotal formula to get a "1" in every row. However, my results do not change when filtering. I think it's because it might not be putting a "0" in the rows that are filtered off. Not sure how to check that.

EDIT: This seemed to have worked! My formulas were on manual so I had to manually calculate each sheet. Thanks!