Summarizing data from one worksheet to another

[Scott Lyon]

This is (hopefully) an easy question (with an easy answer), but I'm not
having much luck figuring it out on my own.


I've got a workbook that consists of two worksheets: Data and Summary.

Column A of Data is a user-enterable column called "Date"
Column B of Data is a user-enterable column called "Task"
Column C of Data is a user-enterable column called "Total"


The user will enter the data in any order, and there will be many repeats of
each "Task" they enter.


On the second worksheet, I want to be able to display a list of each unique
Task they enter, given a date range (using the Date column - in fact, the
range would need to be exactly a week). Then I also want a sum of the Total
column, for that Task, for that date range.


Is there a relatively-easy (read: quick) way I can do that?


To explain a little better, here is some sample data:


Date Task Total
--------------------------
7/11/04 SIT 1
7/12/04 TEST 5
7/13/04 RUN 6
7/14/04 TEST 2
7/15/04 TEST 1
7/15/04 WAIT 4
7/16/04 TEST 3
7/17/04 WALK 7
7/18/04 RUN 8

Given a date range of 7/11/04 - 7/17/04 (so excluding the 7/18 RUN task
automatically), I'd like to see the following results (on the Summary
worksheet):

Task Total
----------------
SIT 1
TEST 11 (5+2+1+3)
RUN 6
WAIT 4
WALK 7


Thanks!

 

[Frank Kabel]

Hi
use a pivot table for this and group by the date column. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Peter Atherton]

Scott

Array formula will work. Create a list of the task in
column E and type the list of each task once.

Date Task Total

11/07/04 SIT 1 SIT 1
12/07/04 TEST 5 TEST 11
01/07/05 RUN 6 RUN 14
02/07/05 TEST 2 WAIT 4
03/07/05 TEST 1 WALK 7
04/07/05 WAIT 4
05/07/05 TEST 3 37
06/07/05 WALK 7
07/07/05 RUN 8

In F3 task SIT enter =SUM(IF(E3=$B$3:$B$11,$C$3:$C$11)) an
an array formula i.e Ctrl + Enter and copy the formula down

Regards
Peter

(e-mail address removed)

 

[Domenic]

Hi Scott,

Here's another possible solution I learned from Aladin.

Using your table in your Data sheet as an example:

Summary Sheet...

A1 Enter a 0

A2, copied down:

=IF((Data!A2<>"")*ISNA(MATCH(Data!B2,Data!$B$1:B1,0)),LOOKUP(9.9999999999
9999E+307,$A$1:A1)+1,"")

B1:

=LOOKUP(9.99999999999999E+307,A$1:A$10)

C2, copied down:

=IF(ROW()-ROW($C$2)+1<=$B$1,INDEX(Data!B:B,MATCH(ROW()-ROW($C$2)+1,A$1:A$
10,0)),"")

D2, copied down:

=IF(C2<>"",SUMPRODUCT((Data!$A$2:$A$10>=$E$2)*(Data!$A$2:$A$10<=$F$2)*(Da
ta!$B$2:$B$10=C2)*(Data!$C$2:$C$10)),"")

where E2 contains lower limit for your date as criteria and F2 the
higher limit

Hope this helps!