I have a worksheet named "data". i am entering date, qty, job#, and staff. On the next sheet i want to automatically calculate for each month the count of job#'s and total qty by month. I don't know how to sort and calculate the data sheet to give me these totals. On another worksheet i want to do the same thing but separate them by staff. I need some guidance. Thanks
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Formulas by month/quarter/staff
- Thread starter pe
- Start date
You can easily accomplish either of these tasks by using Data-Subtotals right on the SAME sheet.
DaleWatson123321
Programmer
pe,
I believe Excel's powerful "database functions" are the best method of providing the solution you're seeking.
I would suggest you first read my postings in the following two threads: thread68-270562 and thread707-270238.
Then, if you would like the example file referred to in thread68-270562, please feel free to ask.
Another option, is for you to send me a copy of your file, preferably with a “roughed-out” summary page (or pages) showing the type of summary results you want. Don’t worry about the formulas - I’ll look after those. Just place onto a separate sheet, a “matrix” of the categories and headings you require for each summary report – i.e. one summary per sheet.
If there is a problem with “sensitive” data, please replace such data with fictitious data that still provides a “relevant” and sufficient example of the type of data you’re dealing with.
I can be reached at my HOME address below.
Regards, ...Dale Watson
HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca
I believe Excel's powerful "database functions" are the best method of providing the solution you're seeking.
I would suggest you first read my postings in the following two threads: thread68-270562 and thread707-270238.
Then, if you would like the example file referred to in thread68-270562, please feel free to ask.
Another option, is for you to send me a copy of your file, preferably with a “roughed-out” summary page (or pages) showing the type of summary results you want. Don’t worry about the formulas - I’ll look after those. Just place onto a separate sheet, a “matrix” of the categories and headings you require for each summary report – i.e. one summary per sheet.
If there is a problem with “sensitive” data, please replace such data with fictitious data that still provides a “relevant” and sufficient example of the type of data you’re dealing with.
I can be reached at my HOME address below.
Regards, ...Dale Watson
HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca
For ease of use, I would use a pivot table. Go through the wizard and drag and drop "Month" into your column fields and count of Job# and SUM of quantity into the value fields
If you put Staff as the 2nd column field, it will automatically separate by Staff and by Month.
HTH
Geoff
If you put Staff as the 2nd column field, it will automatically separate by Staff and by Month.
HTH
Geoff
- Status
