How to fix formula "=subtotal(3,B$2:B2)"

[tangangtanga]

Hi everyone,

I need your help in fixing the above formula. The thing is, I have an expense table(Using the insert > insert table), I used the above formula for COL A and then COL B for the dates, the problem is that when I filtered my data in the Dates COL the newly added data will be included also in the filter even if the data doesn't match the filter and sometimes after I deleted the filter some of my records are missing which means it was hidden automatically.

Sample output after filtering the COL B to 4/01/2011,

A B
1 4/01/2011
2 4/01/2011
3 4/01/2011
4 4/01/2011
5 9/01/2012

Please help. I tried searching it in google, using a vba function it didn't work either.

[dilipandey]

Hi tangangtanga,

Please upload a sample file to support your query. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

[tangangtanga]

Hi,

Please see attached workbook this is the actual workbook that I'm workin on right now. Don't mind the summary sheets just the other sheets.

[tangangtanga]

Please help..

[Pauleyb]

Strange one. If I filter for 4/1/12, I get one extra row with a date of 11/1/12. If I clear the filter (by choosing 'Select All') and then filter again for 4/1/12, I now get two extra rows. This continues to add a row for each unfilter/filter iteration. Looks like your table is referencing the correct range. I'll have to keep playing with it.

Pauley

[tangangtanga]

@Pauley

Yeah the thing is ITS WEIRD wahaha.. When I started filtering some records are automatically hidden. The workaround that I did is every time I will filter the table I will convert it to range and if I will enter a new record I switch it back to table but thanks for looking into it.

[Pauleyb]

I don't know why, but your NO column seems to be causing the issue. When I copy/pasted your table without that column, the filtering worked as expected. When I then added that column to the table, with that same SUBTOTAL formula, the same odd filtering was observed. Nice trick, trying to keep the NO tied to the line number of the displayed table, but Excel does not seem to like it. It appears you also determined this is what was causing the issue since you title calls out that equation.

If I use a different formula (which does not meet your goal), like =ROW()-1, then the filtering works with one caveat. If some erroneous rows had been shown previously (i.e. the 11/1/12 dates) in a filter, then I change the formula, then those rows continue to show up when filtered. I have to 'reset' the table to make them be properly filtered again.

Been trying to think of a substitute to the SUBTOTAL formula, but nothing comes to mind. This seems to be an Excel bug.

[tangangtanga]

@Pauleyb

Can I sue Microsoft for this bug? kidding aside.. Yeah seems this is a bug using the very convenient formula for auto numbering. btw thanks for dropping by.

[Tony Valko]

I got it to work properly by...

1. Moving column A out of the filtered area. In other words, don't apply the filter to column A.

2. Converting the table to a normal range and then applying autofilter to the range.

[Tony Valko]

I was able to reapply the table to the range B1:M468 and it continued to work properly.

[tangangtanga]

@Tony

Yeah.. I already used the #2 workaround but thanks for your time.

[Tony Valko]

Good deal. Thanks for the feedback!