Totalling with VLOOKUP

[mbys14932]

Hi All,

Hope you are all OK.

I have a little problem that I cannot seem to solve!

The problem I am having is that I would like to total using the VLOOKUP (or any other command, which i do not know off).

Here is the scenario

I have two sheets.

In sheet 1 have:
Column A with the weeks (date).
Column B with numbers.
Column C with numbers.

In sheet 2 I have:
Cell A1 with the date (which I type in)
Cell A2 displays the number (according to the date that I have typed in Cell A1)

In Cell A5 I would like to display the total. If in Cell A1
I type 13/04/09 it would total 30 (06/04/09 & 13/04/09)

I have attached a spreadsheet for reference.

Hope I have not confused you totally!

Thanks in advance.

[zbor]

is this what you need?

In sheet 2, A5 =SUMPRODUCT(1*(Sheet1!A1:A3<=$A$1);1*(Sheet1!B1:B3))

Edit: only this is for 2nd column in 1st sheet... if you want third column just change into:

=SUMPRODUCT(1*(Sheet1!A1:A3<=$A$1);1*(Sheet1!C1:C3))

(or extend further for more rows as you need

[mbys14932]

Thanks for the quick reply zbor, that has sorted it!

Just for reference if anybody else wanted to use the forumula,
they will have to change the semi colon (';') to a comma (',')

eg
In sheet 2, A5 =SUMPRODUCT(1*(Sheet1!A1:A3<=$A$1),1*(Sheet1!B1:B3))

Edit: only this is for 2nd column in 1st sheet... if you want third column just change into:

=SUMPRODUCT(1*(Sheet1!A1:A3<=$A$1),1*(Sheet1!C1:C3))

Thanks.