Forum Discussion
Filtering a table with formulas
- 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?
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.
