Model that can change when data is filtered

[ACF0303]

I have built a model that is linked to a sheet of data. What I would like to do is let someone filter the data, say by state and then have the model change from showing all results to just those filtered results. The tricky part is that I need to use the countif and sumif functions to tell the resulting data where to go in the model. So, for example, I have a bunch of data in column D. The data could get filtered by columns A- C. The model then says, count the data points in column D if column I is "31." I have tried some of the suggestions offered, but I'm doing something wrong. I would be grateful for any suggestions.

 

[RonB1111]

Instead of countif, try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2,ROW(D2:D5000)-ROW(D2),0)),--(I2:I5000<>""))

<colgroup><col width="64"></colgroup><tbody>
</tbody>

This assumes the headers are in row 1 and the data in rows 2 thru 5000 - adjust to your actual data.

For the sumif formula, change the 3 to 9 like this:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D2,ROW(D2:D5000)-ROW(D2),0)),--(I2:I5000<>""))

<colgroup><col width="64"></colgroup><tbody>
</tbody>

 

[ACF0303]

Instead of countif, try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2,ROW(D2:D5000)-ROW(D2),0)),--(I2:I5000<>""))

<tbody>
</tbody>

This assumes the headers are in row 1 and the data in rows 2 thru 5000 - adjust to your actual data.

For the sumif formula, change the 3 to 9 like this:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D2,ROW(D2:D5000)-ROW(D2),0)),--(I2:I5000<>""))

<tbody>
</tbody>

Thanks so much! One quick clarification....what does the <>"" indicate? Would I simply adjust to =31?

 

[RonB1111]

My apologies - yes, it should have been ...=31 and not ...<>"" for your dataset.

 

[ACF0303]

Thanks so much! Works like a charm. Have you had issues with Excel running out of resources to do the calculations?

 

[RonB1111]

You're welcome.

Excel has run out of resources for me sometimes if I've used thousands of volatile functions like VLOOKUP or SUMPRODUCT or others. Sometimes I could switch to other functions and sometimes I could break up the file into 2 or more files. What is the situation you're experiencing?

 

[ACF0303]

So far the model has a number of macros, sum, countif, etc and 45 instances of the formula you helped me with. The challenge is if I have 100000+ lines of data. Am I heading into "have a programmer build something" territory.

 

[RonB1111]

You can try to find out what is causing the slowdown by systematically deleting one thing and seeing if that speeds up the worksheet. For example, using a copy of your slow worksheet (don't do this with the original) apply "copy/paste-values" to a column or group of columns with formulas and see if there's a noticeable improvement in speed. Continue the copy/paste-values with additional columns until you either find the problem or confirm the formula's are not the cause. Disable all macros to see if that's the cause - if it is, then zero in on which specific macro(s) are the cause. Delete pivot tables to see the speed impact. Delete 90,000 of the 100,000 rows to see it's impact on speed. See if hardware might be the problem by running it on a known faster computer, looking at how large in MB is the file size, how much memory your computer has, does it speed up if your computer does not have anything else open, etc.