Hi I have a formula:
Formula:
Please Login or Register to view this content.
Now, when I use filters within the table of data, I have noticed this doesn't adjust the formula result accordingly. How can I get it to Subtotal when filtering data out?
Thanks!
Ok, so I have sort-of found what I need:
Formula:Please Login or Register to view this content.
However, If I were to filter out Column D (customers name) and then add a filter to another column (to take out negative figures), if the total amount sits in the last line (which may then be filtered out because it has a negative figure in one of the other columns), no total shows up at all. Is there any way around this??
I have attached an example spreadsheet with the filter on "Running Balance".
Thanks
Hi,
Not quite sure what your intention is here. I understand your issue here in that your current set-up wil only display the subtotal for each Customer alongside the last occurrence of that Customer in your list.
But what are you wanting the value in column L when filtered to be? The subtotal of just those viewable (i.e. filtered) entries in column L for that Customer? Or, regardless of which of the several e.g. CUST3 entries are showing, the subtotal of all CUST3 entries, whether filtered or not?
Regards
Column L tells me the total value of the customers orders that can be shipped once I filter out the negative stock (negative means we don't have it). However, we don't ship goods if the total value is less than $100, which is why I need a subtotal for each customer of only the items being shown.
Thanks for your help
Hi,
I'm still not entirely sure I understand what you want here, but perhaps (in L2 and copy down):
=IF(ROW(D2)=LOOKUP(10^10,IF(IF(SUBTOTAL(109,OFFSET($K$2:$K$100,ROW($K$2:$K$100)-MIN(ROW($K$2:$K$100)),0,1)),$D$2:$D$100)=D2,ROW($D$2:$D$100))),SUMPRODUCT(--($D$2:$D$100=D2),SUBTOTAL(109,OFFSET($K$2:$K$100,ROW($K$2:$K$100)-MIN(ROW($K$2:$K$100)),0,1))),"")
If that isn't what you were after, I think the best thing would be for you to re-attach your workbook with a few more examples and your desired results (after filtering) in each case.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks