Subtotal and Countifs

**pisgah** · 09-05-2013, 09:34 AM

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** · 09-05-2013, 01:23 PM

I solved the problem by adding named ranges to organize things better and letting the workbook recalc all the way through. Too often I get impatient and just do Shift + F9 instead of F9.

**oeldere** · 09-05-2013, 02:14 PM

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...mproducts.html