If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating daily totals from multiple worksheets (Excel 2003)
Good Morning,
I am working on a stat log that needs to provide daily, weekly and monthly totals for each staff member. I need excel to count the # of "x" in D61000 (for each worksheet) if the date in A6:A1000 is 4/1/2010. I have tried several different functions/formulas and I have not received the response I am looking for. Thanks in advance for your help! Desireé |
#2
|
|||
|
|||
Calculating daily totals from multiple worksheets (Excel 2003)
Select all your sheets that have data (Use Shift and/or Ctrl)
In an unused cell (say Z1) input: =SUMPRODUCT(--(D61000="x"),--(A6:A1000=DATEVALUE("4/1/2010"))) This will place this formula in all the data sheets. On your summary sheet, input something like: =SUM('Sheet 1:Sheet 5'!Z1) This is a 3D formula. -- Best Regards, Luke M "Dhardy" wrote in message ... Good Morning, I am working on a stat log that needs to provide daily, weekly and monthly totals for each staff member. I need excel to count the # of "x" in D61000 (for each worksheet) if the date in A6:A1000 is 4/1/2010. I have tried several different functions/formulas and I have not received the response I am looking for. Thanks in advance for your help! Desireé |
#3
|
|||
|
|||
Calculating daily totals from multiple worksheets (Excel 2003)
You can adapt this to your needs based on a list of sheet names in col F.
Also works with a define name =SUMPRODUCT(SUMIF(INDIRECT(OFFSET(LU!F1,,,COUNTA(L U!F:F))&"!B1"),"PMI",INDIRECT(OFFSET(LU!F1,,,COUNT A(LU!F:F))&"!A3"))) -- Don Guillett Microsoft MVP Excel SalesAid Software "Dhardy" wrote in message ... Good Morning, I am working on a stat log that needs to provide daily, weekly and monthly totals for each staff member. I need excel to count the # of "x" in D61000 (for each worksheet) if the date in A6:A1000 is 4/1/2010. I have tried several different functions/formulas and I have not received the response I am looking for. Thanks in advance for your help! Desireé |
#4
|
|||
|
|||
Calculating daily totals from multiple worksheets (Excel 2003)
Suppose in your Summary sheet you use 2 cells to record the variables
- eg. put "x" in A1 and the date 4/1/2010 in B1. Then you can Group all the other sheets together (select the first of those sheets, hold down the CTRL key and click on the tab for the last sheet), ensuring that the Summary sheet is outside the selected group of sheets. Then you can enter this formula in a particular cell (eg X1): =SUMPRODUCT(--(D$6$1000=Summary!A1),--(A$6:A$1000=Summary!B1)) As the sheets are all grouped together this formula will be placed into that same cell in every sheet, but you will only have to type it once yourself. The formula will give you a count for each sheet. Now you should ungroup the sheets by clicking on the tab for the Summary sheet (or you could right-click on one of the sheet tabs and click Ungroup Sheets). Then in C1 (say) of your Summary sheet you can enter: =SUM(first:last!X1) where first and last are the names of your sheets that form the range you want to count from - you could insert two blank sheets called first and last and position first so that it is after the Summary sheet but before the other sheets and position last so that it is at the end of your sheets (thus forming a "sandwich" of sheets with Summary outside them). This would enable you quite easily to move sheets into or out of the sandwich to model different situations if you wished to. Now, you can easily change the "x" in A1 or the date in B1 to get a new count. If you have other conditions that you want to count then you could put these in A2 and B2, and use a similar formula in X2 of the detailed sheets, and so on. Hope this helps. Pete On Apr 20, 6:27*pm, Dhardy wrote: Good Morning, I am working on a stat log that needs to provide daily, weekly and monthly totals for each staff member. I need excel to count the # of "x" in D61000 (for each worksheet) if the date in A6:A1000 is 4/1/2010. I have tried several different functions/formulas and I have not received the response I am looking for. Thanks in advance for your help! Desireé |
#5
|
|||
|
|||
Calculating daily totals from multiple worksheets (Excel 2003)
Thanks Luke,
Is there a similar formula that will sum the dollar amounts in column P if the date in column A is 4/1/2010. Can you help with that too? Desireé "Luke M" wrote: Select all your sheets that have data (Use Shift and/or Ctrl) In an unused cell (say Z1) input: =SUMPRODUCT(--(D61000="x"),--(A6:A1000=DATEVALUE("4/1/2010"))) This will place this formula in all the data sheets. On your summary sheet, input something like: =SUM('Sheet 1:Sheet 5'!Z1) This is a 3D formula. -- Best Regards, Luke M "Dhardy" wrote in message ... Good Morning, I am working on a stat log that needs to provide daily, weekly and monthly totals for each staff member. I need excel to count the # of "x" in D61000 (for each worksheet) if the date in A6:A1000 is 4/1/2010. I have tried several different functions/formulas and I have not received the response I am looking for. Thanks in advance for your help! Desireé . |
Thread Tools | |
Display Modes | |
|
|