A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating daily totals from multiple worksheets (Excel 2003)



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2010, 06:27 PM posted to microsoft.public.excel.worksheet.functions
Dhardy
external usenet poster
 
Posts: 12
Default 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  
Old April 20th, 2010, 06:54 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default 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  
Old April 20th, 2010, 06:56 PM posted to microsoft.public.excel.worksheet.functions
Don Guillett[_2_]
external usenet poster
 
Posts: 607
Default 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  
Old April 20th, 2010, 07:54 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default 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  
Old April 20th, 2010, 10:46 PM posted to microsoft.public.excel.worksheet.functions
Dhardy
external usenet poster
 
Posts: 12
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.