Using Subtotal Function display Month

  • 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?




    Thanks

  • Re: Using Subtotal Function display Month


    You might want to consider the SUMIF function, if I understand your needs. Put your target month in a cell out of the way (i.e. T2)


    =SUMIF(A:A,T2,J:J)


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!