how to sum up based on dates

[Guest]

Let say I have this file will multiple sheets: Bob, Adam, Shiela

This example in Bob sheet:

Date Activities Venue
3/19/2006 Parents Meeting Branch
3/25/2006 Area Meeting A HQ
4/3/2006 Clients Orientation Branch
5/11/2006 Team Meeting HQ

Adam:

Date Activities Venue
2/8/2006 Clients Oreintation Branch
3/25/2006 Area Meeting B HQ
4/17/2006 Team Meeting HQ
4/28/2006 Staff Training HQ

How to make a summary in a different page based on
dates/activities/Venue/name?

Thank You (^_^)

 

[Bob Phillips]

Put the names in a list, and use formulae such as

=SUMPRODUCT(--(INDIRECT("'"&A2&"'!A2:A100")>=--"2006-03-01"),
--(INDIRECT("'"&A2&"'!A2:A100")<=--"2006-03-31"))

to get the data. This one counts how many in March.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

How to make a summary in a different page based on

dates/activities/Venue/name?

Here's a slightly different take on the above <g> ..
and a play using non-array formulas

which drives out in a sheet: X
an auto-consolidation from the 3 source sheets (stacked)

and then in a sheet: Y,
an auto-sort of X in chronologic sequence by dates

A sample construct is available at:
http://www.savefile.com/files/3089838
Auto-consol n sort data fr multiple sheets by dates.xls

Assume 3 identical structure source sheets named: Bob, Adam, Sheila,
col headers in A1:C1 : Date, Activities, Venue,
data from row2 down to a max expected row10

In Bob,
Put in D2: =IF(A2="","",ROW())
Copy down to D10

In Adam,
Put in D2: =IF(A2="","",ROW()+MAX(Bob!D))
Copy down to D10

In Sheila,
Put in D2: =IF(A2="","",ROW()+MAX(Adam!D))
Copy down to D10

In a new sheet X:
Col Headers in A11 : Date, Activities, Venue, Name

Put in A2:
=IF(ISERROR(SMALL(Bob!$D:$D,ROW(A1))),
IF(ISERROR(SMALL(Adam!$D:$D,ROW(A1)-COUNT(Bob!$D:$D))),
IF(ISERROR(SMALL(Sheila!$D:$D,ROW(A1)-(COUNT(Bob!$D:$D)+COUNT(Adam!$D:$D)))),"",
INDEX(Sheila!A:A,MATCH(SMALL(Sheila!$D:$D,ROW(A1)-(COUNT(Bob!$D:$D)+COUNT(Adam!$D:$D))),Sheila!$D:$D,0))),
INDEX(Adam!A:A,MATCH(SMALL(Adam!$D:$D,ROW(A1)-COUNT(Bob!$D:$D)),Adam!$D:$D,0))),
INDEX(Bob!A:A,MATCH(SMALL(Bob!$D:$D,ROW(A1)),Bob!$D:$D,0)))

Copy A2 to C2

Put in D2:
=IF(ROW(A1)-1<COUNT(Bob!D),"Bob",IF(ROW(A1)-1<COUNT(Adam!D)+COUNT(Bob!D),"Adam",IF(ROW(A1)-1<COUNT(Sheila!D)+COUNT(Adam!D)+COUNT(Bob!D),"Sheila","")))

Select A22, copy down to D30

X gathers and returns the results from the 3 source sheets, in this sequence:
Lines from Bob, then those from Adam, then those from Sheila (stacked)

In a new sheet Y:
Col Headers in A11 : Date, Activities, Venue, Name

Put in A2:
=IF(ISERROR(SMALL($E:$E,ROW(A1))),"",
INDEX(X!A:A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0)))
Copy A2 to D2

Put in E2:
=IF(X!A2="","",X!A2+ROW()/10^10)
Select A2:E2, copy down to E30

Y returns the results from the 3 source sheets,
sorted in chronologic sequence by the dates in col A

---