Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formulas sorting by Month and Quarter (Excel xp/win2000)

    I have this workbook enclosed. I need help with the formulas on the monthly and quarterly tabs. How do I look on the data worksheet and pull out quantities and counts by month or quarter? Also, i want to sort by staff and do a sheet that shows what each person submitted. Any help with these formulas would be greatly appreciated. thanks
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Re: Formulas sorting by Month and Quarter (Excel xp/win2000)

    The number of jobs, for example, could be handled with this array formula (CTRL+SHIFT+ENTER):

    =SUM(0+(3=MONTH(DailyData!$A$2:$A$100)))

    (for March=3).

    The mo'ly NumBatch would be something like this array formula:

    =SUM((DailyData!$E$2:$E$100)*(3=MONTH(DailyData!$A $2:$A$100)))

    etc., etc.

    HTH.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formulas sorting by Month and Quarter (Excel xp/win2000)

    Instead of using complicated formulae, consider a Pivot Table, which could provide all the reporting you require. I included some extra columns in your data so that the month and quarter summaries can be built easily in a pivot table. See the attached file. I also created a named range (Database) on the DailyData worksheet which acts as the data souce for the pivot table. The named range is based on the following formula

    =OFFSET(DailyData!$A$1,0,0,COUNTA(DailyData!$A:$A) ,COUNTA(DailyData!$1:$1))

    which means it should expand dynamically as data is added. You then just need to refresh the pivot table after adding new data.

    Andrew C
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •