Using Numbers v5.2 (5869)
Q1 – Working on a dataset of about 1500 Rows and 35 columns. Applied 5 Filters with a couple of ‘or’ conditions in each. Cannot add any other filters – Add a Filter option is greyed out in the Filters tab [next to Categories, Sort]. Is there a limit to the number fo filters which can be applied?
Q2 – Categories are back in this version ! For the same dataset as above, when Subtotaling a Category post-filter, the subtotal does not reflect the filtered data, but the total dataset. Is it feasible to get the subtotal for the category accounting for the filter applied?
—–
Nav
Yes. There does appear to be a limit to the number of filters. Maybe instead of using multiple filters, have formulas in columns that perform calculations that return true or false, and then filter based on that one column. For instance, column HH could check to see if column B is > 3 and column R is < 20 and column Z is "something." Then the filter is simply looking at column HH being true. As for category filters, you can get subtotals in the same row as the category. So if you add a category "something" you'll see a row with that category that you can expand. Click on a cell in that row and you'll see you can add subtotals, averages and a variety of other things. So it isn't in a footer or header row, it is in the category row. I'll have a video on this soon. I've already added one to my Numbers course, but I'll do a regular MacMost video on it as soon as I cover all the new iOS and Mojave stuff.
Thanks a lot Gary. That is very helpful.
For filters - thanks for confirming the limitation. Hopefully it gets de-limited in upcoming versions.
For the second question on categories - Did use the Category Row to get the Subtotal, but it still reports the total for the dataset. As an example - Categorized by State, and products under State are Old or New. Filtered out Old, and the State subtotal still reports the Total for both old & new.
Thanks again. Will lookout for the video !
Nav: I doubt they will ever lift the limit since the method I suggest is probably the right way to go for lots of filters. As for the other problem, I'm not sure what could be the issue. It works for me. Perhaps share the spreadsheet via file sharing service and send me the link to check it out.
Gary - Using the defualt template within Numbers called Categories [under Basic]. Used one of the blank columns [D for instance] to input Old/New for each item categorized under Andy and Chloe. Then filtered Column D for New only, and the Subtotal for Amount should update, but stays unchanged at 150 and 125.
Nav: Oh, wait. So you are applying a filter? Sorry, Ii missed that. Filters just hide things, they don't remove them from calculations. If you want to calculate based on the value of a column, you either need to apply that as an additional category, or use a SUMIF formula or the like.