date function

[drkenne]

Need formula to sum dollar values in a column based on
column with dates in a certain range. Looking to do
something like this:

=sum(A:A)if(B:B) between xx/xx/xx and xx/xx/xx

DK

 

[Jason Morin]

=SUMPRODUCT(A1:A10*(B1:B10>="10/6/04"+0)*
(B1:B10<="10/12/04"+0))

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi Jason
this kind of formula would break in a non-US environment
Better to use
=SUMPRODUCT((A1:A10)*(B1:B10>="2004-10-6"+0)*(B1:B10<="2004-10-12"+0))

or using the dATE function

 

[Guest]

This didn't seem to work. It summed the entire column,
regardless of date range. Any other suggestions? The
date format is xx/xx/xxxx. Is ther a way to use a
wildcard and search only on the year?

 

[Govind]

Hi,

You can try

=SUMPRODUCT((A1:A10)*(year(B1:B10)=2004))

Regards

Govind.

 

[Frank Kabel]

Hi
this formula should work. Are you sure you have real date values. e.g.
does the following formula return true?
=ISNUMBER(B1)

 

[DRKENNE]

It returns a true value. Date on the spreadsheet is in the following format:
mm/dd/yy. Here's an example of a data in a row:

C D E F G
H
$900.00 $0.00 $900.00 $900.00 04/23/99 06/05/00

What I trying to do is sum the dollar values in a column (either C, D,or E)
if the dates in either G or H are between a certain range.

Using this formula:
=SUMPRODUCT((C2:C250)*(G2:G250>="1999-01-1"+0)*(G2:G250<="1999-12-31"+0))
returns this error: #VALUE!