Dynamic Offset Summarize Pivot Data

Hello All,

I have been struggling with, what I thought was pretty straight forward, problem.

Basically, I have a large excel file with many many tabs, all with pivots that summarize similar data, and vary in number of rows. Lately I have been using a simple sumproduct equation to match values in the "volume" column with the minimum Market Share requirement. It works fine, but it is tiresome to update every tab all the time, I would like a more elegent way. I figured all I would need to do is a simple sumproduct statement with a dynamic offset embedded. But it didn't work.

I have attached a dummy data set with the pivot and how my file is generally structured...any thoughts??

Many thanks!
Matt