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.
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.
---------------------------------------------------
ONLY APPLIES TO VBA RESPONSES WHERE APPROPRIATE
To insert code into the VBE (Visual Basic Editor)
- Copy the code.
- Open workbook to paste code into.
- Right click any worksheet tab, select View Code
- VBE (Visual Basic Editor) opens to that sheets object
- You may change to another sheets object or the This Workbook object by double clicking it in the Project window
- In the blank space below the word "General" paste the copied code.
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks