Vlookup Sum

[Guest]

Hi, I'm having troubles summing Vlookups, my worksheet has:
A B
5-May-05 2300
6-May-05 1200
7-May-05 2500
8-May-05 2300
9-May-05 1500
10-May-05 2000

I need to SUM the amount of a period like 5 to 9-May-05 using a Vlookup, I'm
trying sometinhg like SUM(Vlookup...:Vlookup...), but it dosen't work. Could
someone help me. Thanks!!

 

[Arvi Laanemets]

Hi

VLOOKUP returns a single value - as follows the sum of vlookup equals to
lookup value. And to use the vlookup result as a range doesn't work at all.

When values in column A are really dates, then try something like this
(adjust the datarange to your table):
=SUMPRODUCT(--(A1:A100>=DATE(2005,5,5)),--(A1:A100<=DATE(2005,5,9)),B1:B100)

 

[Guest]

Thanks for your reply. I thought that I should use a VLOOKUP function for
this matter.
Sorry to disturb you again but the formula gives me 0 (zero), I can't get
the amount that I want. Perhaps I'm doing something wrong!!
Can you help me again??


"Arvi Laanemets" escreveu:

 

[Guest]

Hi
Are you sure that your 'dates' are Excel dates? The formula would return a
zero if they weren't.

 

[Arvi Laanemets]

Hi

To check dates in your table being real Excel dates, format some cell with
date in it as general. When the date in cell is replaced with integer, it is
date. When the cell content remains same, you have there a datestring (a
text string which looks like a date).

When you have in your table datestrings instead of dates, and those
datestrings are in form the excel recognizes as date format (I can't test
your data, because recognized date formats depend on regional setings), the
try this formula:

=SUMPRODUCT(--(DATEVALUE(A1:A100)>=DATE(2005,5,5)),--(DATEVALUE(A1:A100)<=DA
TE(2005,5,9)),B1:B100)
(I'm not sure how it will work)

A better solution is to convert your datestrings to dates - you have to use
helper columns to do so. P.e. to convert datestrings in range A1:A100 :
1) format range a1:a100 as General
2) insert a column B
3) into b1 enter the formula
= DATEVALUE(a1)
4) Copy b1 to b1:b100
5) Copy the range b1:b100 and use Paste Special to overwrite datestrings in
range a1:a100 with values (check 'Values' before pressing OK)
6) Format the range a1:a100 as date in desired format (p.e. as Custom
"d-mmm-yy", when it is a valid date format)
7) delete column B
..
Then same for other column. Now my formula from previous answer will work.


Arvi Laanemets