retrieving data from a range of dates

[Neil]

Hi

I have a table that with 2 columns. First column has dates listed, and
the second has values listed.

Is there a formula which will sum up the values table that falls
within certain date periods. i.e.

Dates Values
1/1/10 5
7/1/10 1
23/2/09 3
4/1/10 2

In the above table i want to be able to sum the values that fall
between the 1st and 5th of Jan 2010.

Any help would be greatly appreciated.

Thanks

 

[Pete_UK]

Try this:

=SUMIF(A:A,">="&C1,B:B)-SUMIF(A:A,">"&D1,B:B)

where C1 contains the earlier date (1/01/2010) and D1 contains the
later date (5/01/2010). I assume you want the period to be inclusive
of these dates.

Hope this helps.

Pete

 

[Neil]

Thanks Pete

This works perfectly...

Would you be able to help me with an additional portion of this
formula.

What if i had 3 columns and wanted to find the sum based on matching
the criteria in the first column as well as falling within certain
date periods in the 2nd column.

I tried using the AND function within this but seem to be getting an
error with the formula:

Name Date Value
Tom 1/1/10 1
Steve 1/1/10 2
John 3/1/10 3
Chris 22/12/09 3
Tom 12/1/10 4
Tom 5/1/10 10
Steve 4/1/10 2

=SUMIF(AND((A:A,"="&E1),(B:B,">="&F1)),C:C)-SUMIF(AND((A:A,"="&E1),
(B:B,">"&G1)),C:C)

Where E1 contains Tom, F1 contains earlier date (1/1/10) and G1
contains latter date (5/1/10).

Any suggestions?

Thanks

 

[Pete_UK]

SUMIF can only be used if you have one criterion. If you have more
then you can use SUMPRODUCT, like this:

=SUMPRODUCT((A1:A10=E1)*(B1:B10>=F1)*(B1:B10<=G1),C1:C10)

Note that you can't use full columns with SP (unless you have XL2007).
The ranges must be the same size. In the formula the * is equivalent
to AND.

Hope this helps.

Pete