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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote