VLOOKUP and sums
 

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.

VLOOKUP and sums
 

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

"Gilly B" wrote:

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.

VLOOKUP and sums
 

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" wrote:

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

"Gilly B" wrote:

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.

VLOOKUP and sums
 

Here's a sumproduct version.

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

Mike

"Mike H" wrote:

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" wrote:

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

"Gilly B" wrote:

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.

VLOOKUP and sums
 

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.

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
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

"Gilly B" wrote:

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.