Complex Sum Help

[McDan1el]

Hey All,

Was wondering if you could help me out with an excel sheet - i have a way of doing what i want to achieve, but it is working out to be very difficult and "messy"

Basically i have a data input sheet which has the Day/Date and then loads of columns, from answered calls, lost calls, average talk times... the list goes on...

What i want to be able to do is on a front sheet have the user enter a date, which then causes the sheet to populate with the details for that date - now i know a VLOOKUP works fine, the problem is the date the enter is the week commencing date so i do not just want that line i want that line and the 6 beneath it all added up.

i.e

09/03 - 10
10/03 - 7
11/03 - 6
12/03 - 5
13/03 - 10
14/03 - 12
15/03 - 10

So when i type 09/03 in the "Week Commencing Box" it does not give me 10, but instead gives me 60.

Any one suggest what to do rather then having 7 VLOOKUPS?

Cheers,

Dan

 

[Domski]

Try this maybe:

Book1
	A	B	C	D	E
1	09/03/2009	10		09/03/2009	60
2	10/03/2009	7
3	11/03/2009	6
4	12/03/2009	5
5	13/03/2009	10
6	14/03/2009	12
7	15/03/2009	10
8	16/03/2009	11
9	17/03/2009	13
10	18/03/2009	12
11	19/03/2009	7
12	20/03/2009	13
13	21/03/2009	13
14	22/03/2009	5
15	23/03/2009	5
Sheet1

Dom

 

[lenze]

Why not just create a Pivot Table with the Page Field(Dates)grouped by Days(7)?

lenze

 

[bigturnip]

Can you add a column to your data with something like:

=A1-WEEKDAY(A1,2)+1

which gives you the first day of the week

and then just use a SUMIF on the new column?