I am using the SUBTOTAL function in two different cells. Column J of the spreadsheet contains revenues. In Cell A1 I have this function(=SUBTOTAL(9,J15:J669). Column A displays the calendar month. In Cell B1 I am using this function (=SUBTOTAL(1,A15:A669).
When I filter on a particular month I will get the visible month (May05 for example) in B1 and in the A1 I will get the monthly total.
A1 = 1,000
B1 = May
However, when the filter is off, B1 displays Jan-05 since all the months from Oct to May are visible (our fiscal year is from Oct to Sept).
However, when the filter is off the cell displays Jan-05. Is this because it is the default. because more than one month is being shown. Is there a way around this, perhaps by using another function?