Forum Discussion
Solved
Filtering a table with formulas
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: ...
- Maybe the AGGREGATE function might help, as you can ignore hidden rows with it
Maybe the AGGREGATE function might help, as you can ignore hidden rows with it
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.
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).
- Could you post an example in an Excel file?
