retrieving data from a range of dates

  • Thread starter Thread starter Neil
  • Start date Start date
N

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
 
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
 
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
 
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
 
Back
Top