VLOOKUP and sums

[Gilly B]

Any help greatfully received !!! If I have a list of data as below ( eg )
1/1/08 10
8/1/08 15
15/1/08 20
22/1/08 30
29/1/08 40

The list has 'dates' in column A and numbers in column B and will always be
in this format. How do I creata a formula that can add up column B total, but
for a certain group of dates - ie the total of B for dates between and
including 8/1/08 and 22/1/08. Obvioulsy I can expand the range, but thought
this the easiest example of what I am trying to achieve !!
Thanking anyone in advance.

 

[Mike H]

Gilly,

There are a couple of ways and here's one

=SUMIF(A1:A10,">="&C1,B1:B10)-SUMIF(A1:A10,">="&D1,B1:B10)

Where C1 is the start date and D1 is the end date of your sum range

Mike

 

[Mike H]

Hi,

Forgot to mention that Excel may try and be 'helpful' and format this
formula as a date. If it does reformat as general.

Mike

 

[Mike H]

Here's a sumproduct version.

=SUMPRODUCT((A1:A10>=C1)*(A1:A10<=D1)*(B1:B10))

Mike

 

[T. Valko]

for dates between and including 8/1/08 and 22/1/08.

=SUMIF(A1:A10,">="&C1,B1:B10)-SUMIF(A1:A10,">="&D1,B1:B10)

To include the boundary dates the 2nd SUMIF criteria should be ">"&D1.