SOLVED

Filtering a table with formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2104044%22%20slang%3D%22en-US%22%3EFiltering%20a%20table%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2104044%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20know%20how%20I%20could%20possibly%20filter%20my%20table%20but%20have%20the%20table%20update%20the%20formulas%20that%20are%20in%20it%20so%20as%20not%20to%20include%20the%20filtered%20out%20section%20of%20the%20table.%26nbsp%3B%3C%2FP%3E%3CP%3EShort%20example%3A%3C%2FP%3E%3CP%3EI%20have%20an%20invoice%20column%2C%20cumulative%20column%2C%20and%20average%20column.%20The%20invoice%20is%20inputted%20by%20hand%20and%20then%20the%20cumulative%20and%20average%20column%20work%20off%20of%20that%20data.%20There%20are%2012%20rows%20representing%20each%20month%20of%20the%20year.%26nbsp%3B%3C%2FP%3E%3CP%3ESometimes%20an%20order%20doesn't%20begin%20in%20january%2C%20therefore%20I%20would%20like%20to%20filter%20out%20the%20months%20before%20it.%20Lets%20say%20I%20filter%20out%20to%20June%20because%20that%20is%20when%20the%20order%20began%20and%20I%20received%20an%20invoice.%20I%20run%20into%20a%20problem%20where%20my%20cumulative%20and%20average%20columns%20still%20attempt%20to%20calculate%20the%205%20rows%20taken%20out%20above%20them.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20spreadsheet%20is%20much%20more%20complicated%20with%20VLOOKUP%20in%20some%20of%20the%20beginning%20rows%20but%20I%20beli%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2104044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2104871%22%20slang%3D%22en-US%22%3ERe%3A%20Filtering%20a%20table%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2104871%22%20slang%3D%22en-US%22%3EMaybe%20the%20AGGREGATE%20function%20might%20help%2C%20as%20you%20can%20ignore%20hidden%20rows%20with%20it%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2106511%22%20slang%3D%22en-US%22%3ERe%3A%20Filtering%20a%20table%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2106511%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3Bthank%20you%20this%20did%20help.%20I%20was%20able%20to%20apply%20it%20to%20the%20cumulative%20tabs%20and%20now%20I'm%20researching%20how%20to%20use%20it%20with%20lots%20of%20IF%20statements.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, 

I would like to know how I could possibly filter my table but have the table update the formulas that are in it so as not to include the filtered out section of the table. 

Short example:

I have an invoice column, cumulative column, and average column. The invoice is inputted by hand and then the cumulative and average column work off of that data. There are 12 rows representing each month of the year. 

Sometimes an order doesn't begin in january, therefore I would like to filter out the months before it. Lets say I filter out to June because that is when the order began and I received an invoice. I run into a problem where my cumulative and average columns still attempt to calculate the 5 rows taken out above them. 

The spreadsheet is much more complicated with VLOOKUP in some of the beginning rows but I beli

5 Replies
Best Response confirmed by bgrimshw37 (New Contributor)
Solution
Maybe the AGGREGATE function might help, as you can ignore hidden rows with it

@Wyn Hopkins thank you this did help. I was able to apply it to the cumulative tabs and now I'm researching how to use it with lots of IF statements. 

@Wyn Hopkins My searches have been futile so far. Would you happen to know how to use aggregate with IF statements. My original formula was this: =IF(ISBLANK(C32),E31,E31+(C32/B32)). But it doesn't work when I hide rows. 

I changed the formula to this: =AGGREGATE(4,7,IF(ISBLANK(C32),E31,E31+(C32/B32))).  It still works until I add a value to C32. This it gives me the #Value error. 

Could you post an example in an Excel file?

@bgrimshw37 

With first 13 aggregations, in particular with MAX (#4) you shall use references, not formulas. Most reliable way is to add helper column with formula like =(AGGREGATE(3,5,A1) which returns 0 for hided row and 1 otherwise. Here as column A we take any column in each row of which we have any data. Assuming helper column is F, you may use =IF(F1, my formula, 0).