Have a table with days of the year(in sequence) in column 1 and values in
columns 2 -7 I want to enter any two dates and using a vlookup sum the data
in column two

Columns
1 2 3 4 5 6
7

1 Jan 06 24 6 5
2 Jan 06 12 8 2
3 jan 06 0 20 4
4 jan 06 20 6 5

enter 1 Jan and 4 jan the answer = 56

One way is to use SUMPRODUCT ..

Assuming data in cols A to D from row2 down,
with real dates in col A ..

Inputs for dates will be made in F1:G1 (Enter entire dates to avoid
ambiguity):
Start date in F1, eg: 1-Jan-2006
End date in G1, eg: 4-Jan-2006

Then placed in H1:
=IF(OR(F1="",G1=""),"",SUMPRODUCT(($A$2:$A$1000=F 1)*($A$2:$A$1000<=G1),$B$2:$B$1000))

H1 returns the desired result (ie 56 per sample data posted)

As-is, H1 can be copied down to return correspondingly for other pairs of
start-end dates in cols F and G. Adapt the ranges within the SUMPRODUCT (SP)
to suit. Use the smallest range large enough to cover the max expected extent
of source data in cols A and B. Note that we can't use entire col references
in SP.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"hkaempffe" wrote:
Have a table with days of the year(in sequence) in column 1 and values in
columns 2 -7 I want to enter any two dates and using a vlookup sum the data
in column two

Columns
1 2 3 4 5 6
7

1 Jan 06 24 6 5
2 Jan 06 12 8 2
3 jan 06 0 20 4
4 jan 06 20 6 5

enter 1 Jan and 4 jan the answer = 56

With data in columns A and B, and first date in G1 and second date in
H1,

=SUM(OFFSET(B1,MATCH(G1,A:A)-1,0,MATCH(H1,A:A)-MATCH(G1,A:A)+1))

will get you want you want. You don't need a VLOOKUP.

hkaempffe wrote:
Have a table with days of the year(in sequence) in column 1 and values in
columns 2 -7 I want to enter any two dates and using a vlookup sum the data
in column two

Columns
1 2 3 4 5 6
7

1 Jan 06 24 6 5
2 Jan 06 12 8 2
3 jan 06 0 20 4
4 jan 06 20 6 5

enter 1 Jan and 4 jan the answer = 56

You may want to use Max's formula since it is more general - it does
not require the dates to be sequential, which mine does - but if the
days are always sequential, either should satisfy your needs.

DOR

hkaempffe wrote:
Have a table with days of the year(in sequence) in column 1 and values in
columns 2 -7 I want to enter any two dates and using a vlookup sum the data
in column two

Columns
1 2 3 4 5 6
7

1 Jan 06 24 6 5
2 Jan 06 12 8 2
3 jan 06 0 20 4
4 jan 06 20 6 5

enter 1 Jan and 4 jan the answer = 56

Good point, thanks.
I failed to advertize that earlier <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DOR" wrote:
You may want to use Max's formula since it is more general - it does
not require the dates to be sequential, which mine does - but if the
days are always sequential, either should satisfy your needs.

DOR