Can I set the criteria in a sumif statement as a cell reference?

[Duncan]

I am trying to use the sumif statement in excel and want to set the criteria
to a "less than" date. The only way I can get the formula to work is by
hardcoding the date into the sumif statement i.e
=sumif(A1:II1,"<31-OCT-05",A2:II2). However I want to copy this formula using
a number of different dates and do not want to individually hard code the
date. I want to replace the hard coded date with a cell reference that
contains the date. When I do this I get the microsoft excel pop up box
telling me there is an error in my formula.

[Excelenator]

I used this formula

=SUMIF(A1:A9,D2,B1:B9)

Where Cell D2 was formatted as a date and I input "<2/27/1991" into that cell just as it appears without the quotes and the sum worked.

[MarkN]

Hi Duncan,

How about =SUMPRODUCT(--(A2:A4>F1),B2:B4) where A2:A4 contain the dates, F1
contains the referenced date cell and B2:B4 the values.
--
Thanks,
MarkN


"Duncan" wrote:

> I am trying to use the sumif statement in excel and want to set the criteria
> to a "less than" date. The only way I can get the formula to work is by
> hardcoding the date into the sumif statement i.e
> =sumif(A1:II1,"<31-OCT-05",A2:II2). However I want to copy this formula using
> a number of different dates and do not want to individually hard code the
> date. I want to replace the hard coded date with a cell reference that
> contains the date. When I do this I get the microsoft excel pop up box
> telling me there is an error in my formula.

[JMB]

Where B1 contains the date:
=sumif(A1:II1,"<"&B1,A2:II2)


"Duncan" wrote:

> I am trying to use the sumif statement in excel and want to set the criteria
> to a "less than" date. The only way I can get the formula to work is by
> hardcoding the date into the sumif statement i.e
> =sumif(A1:II1,"<31-OCT-05",A2:II2). However I want to copy this formula using
> a number of different dates and do not want to individually hard code the
> date. I want to replace the hard coded date with a cell reference that
> contains the date. When I do this I get the microsoft excel pop up box
> telling me there is an error in my formula.