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

1. 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

2. 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. 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

Posting Permissions

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