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

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.

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

Hi Duncan,

How about =SUMPRODUCT(--(A2:A4F1),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.

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

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.


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=565424

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

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.