Using the same Data set from a subtotal formula to put into a sumproduct formula

I have a spreadsheet with Subtotals already in it. I've come up with this formula:
=SUMPRODUCT(SUBTOTAL(9,OFFSET($V$3,ROW(V4:V4)-ROW($V$3),0))*(R4:R4=$B$1))

Which I inserted in the cell next to the subtotal formula:
=SUBTOTAL(9,V4:V4)

How do I repeat the SUMPRODUCT formula so it has the same data set range that the next SUBTOTAL uses?

Thanks Flame,
I followed your directions on how to upload the file.

The reason for the change in W118 is because that is where the next subtotal occurs. I want the formula to mirror the subtotal data set so when i filter the report it will filter the same data set that the subtotal filter does.
Yes the formula would execute on every row that column E has the word Total.
Yes i chose Subtotal so it would ignore the hidden rows, when i filter the report based on the date in column Q.

I found a way to insert the formula at every total line, i just need to know how to create the formula so it mirrors the subtotal data set. I found a workaround that works, but it only works as long as the data set is no more than 296 rows. I can change it to more rows if necessary, but then this will slow down the performance of the report. Below is what I did.

=SUMPRODUCT(SUBTOTAL(9,OFFSET($V$2,ROW(V4:V300)-ROW($V$2),0))*(R4:R300=$B$1)*(U3=U4:U300))

To make the last criteria function I added in column U3, "=U4".

So this formula I found on a different excel website. I just plugged it in and then adjusted the reference points to fit the needs of the spreadsheet. The value $v$3 should be $v$2. I don't understand what it all means, but it just seems to work (blind luck I guess).

I haven't tried aggregate. Mostly because like I said before I don't know understand what all of the values mean and do.

Thanks for you help though. I spent two days on this and so I can appreciate the desires to finally solve a puzzle or answer the challenge.

No I want it to ignore all of the HQ other than the one that is reference in cell B1 within the subtotal range.

Will this ignore any filtered rows when I filter on column Q "Rel Dt"? That's why I was using Subtotal instead.

PivotTable is not really an option. This report is currently being distributed to users. There was a request by the users to have the ability to see what a certain HQ had on order versus total orders during certain filtered time frames. Once they filtered the report to the certain conditions, they could then enter different HQ numbers to see the different HQ order quantities would be.

Does helper column method work? Other than that, I'm out of ideas.

No it did not. It didn't error out, but all it did was come back with zeros, even though some of the rows should come back with something other than.

Thanks for giving it a swing. I'll stick with what I've got. It's not ideal, but it seems to do the trick.

Yes it should, but I don't want to have to go to each subtotal row and manually put in the correct data set range. I just did for the first couple of subtotal lines so I could see if it works. How do I construct a formula that I can just paste and drag down so it knows how to properly look for the answer?

Thanks for taking a crack at it.

Maybe eventually they'll offer a wizard where we can enter a formula and have it put in the same way that subtotals are entered.

I got an error message. It doesn't like the last two Quotation marks in the formula sequence below.

IFERROR(MATCH("*Total",$E4:$E$1410,0)-1,ROWS($E4:$E$1410)-1))=$B$1)),"")

I was trying to access the file from an IE browser and got an error message. I copied the formula you listed out and tried to paste it into the spreadsheet and got the error. I tried chrome and was able to access the file. The formula looks good. I'll test it in another report with more rows to see if the formula causes any performance issues. Some of the reports have up to +68K rows.

Thanks for your help!
g-

So it works when I first enter the equation, however as soon as I start to filter the results it cause the values to go blank. And when I try to clear out the filter it doesn't recalculate the values to the appropriate amount.

I guess I could have mentioned that the files can be rather large at times. I had to parse the one I set you down, just so I could post it. I'll stick with what I original came up with since it doesn't seem to be too affected by the range I've set. I might tinker with expanding on the range just to see how much further I can take it before it starts to affect performance.

Thank you.