Calculating daily totals from multiple worksheets (Excel 2003)

[Dhardy]

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é

 

[Luke M]

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.

 

[Don Guillett]

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(LU!F:F))&"!B1"),"PMI",INDIRECT(OFFSET(LU!F1,,,COUNTA(LU!F:F))&"!A3")))

 

[Pete_UK]

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

 

[Dhardy]

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é