Subtotals and Sumproducts

[pisgah]

Hello! First post here. I've done a lot of searching and I want to post this problem because it is frustrating me.

I am trying to do a countifs function for instances where a property falls into a specific range of months since acquisition. I want the to use subtotal so that the count function changes according to filters. Getting it to adjust according to various filters is really the key thing I am trying to do.

The countifs function is:

=COUNTIFS('Data Tape'!$Q$5:$Q$17662,">="&A7,'Data Tape'!$Q$5:$Q$17662,"<="&C7)

I can also express it as sumproduct and get the same result:

=SUMPRODUCT(--('Data Tape'!$Q$5:$Q$17662>=A7),--('Data Tape'!$Q$5:$Q$17662<=C7))

But when I try to add subtotal, the thing keeps returning 0. Here is the formula I am trying to use:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(Q$5,ROW('Data Tape'!Q$5:Q$17662)-ROW(Q$5),0,1)),--('Data Tape'!$Q$5:$Q$17662>=A7),--('Data Tape'!$Q$5:$Q$17662<=C7))

Through trolling similar questions on these forums, I thought I had found the solution. However, I can't get it to work. Any help would be much appreciated. Thanks!

 

[pisgah]

I solved the problem. Still not quite sure why it wasn't working. I added some named ranges and let it recalc all the way through and it worked.