Model that can change when data is filtered

ACF0303

New Member
Joined
Aug 29, 2013
Messages
41
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

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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

New Member
Joined
Aug 29, 2013
Messages
41
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

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
My apologies - yes, it should have been ...=31 and not ...<>"" for your dataset.
 

ACF0303

New Member
Joined
Aug 29, 2013
Messages
41
Thanks so much! Works like a charm. Have you had issues with Excel running out of resources to do the calculations?
 

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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

New Member
Joined
Aug 29, 2013
Messages
41
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

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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.
 

Forum statistics

Threads
1,081,708
Messages
5,360,777
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top