I wrote the following conditional Sum:

=SUM(IF(OC!B3:B9999=Overview!E26,IF(OC!I3:I9999OC !
Date(2007,1,1),IF(OC!I3:I9999<OC!Date(2007,6,6),0) ,0))

Basically, range B3:B9999 has to equate to cell OverviewE26, and the
date has to be between Jan1/07 and Jun 6/06

For some reason the formula keeps erroring out on the statement: IF(OC!
I3:I9999

Any ideas? Thanks!

Shouldn't OC!Date(2007,6,6),0) simply be Date(2007,6,6),0) instead?

Rick


wrote in message
ps.com...
I wrote the following conditional Sum:

=SUM(IF(OC!B3:B9999=Overview!E26,IF(OC!I3:I9999OC !
Date(2007,1,1),IF(OC!I3:I9999<OC!Date(2007,6,6),0) ,0))

Basically, range B3:B9999 has to equate to cell OverviewE26, and the
date has to be between Jan1/07 and Jun 6/06

For some reason the formula keeps erroring out on the statement: IF(OC!
I3:I9999

Any ideas? Thanks!

What are you trying to do with

IF(OC!I3:I9999<OC!Date(2007,6,6),0)

If you're looking for date, loose the 'OC' prefix. DATE isn't a cell
reference.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


wrote in message
ps.com...
I wrote the following conditional Sum:

=SUM(IF(OC!B3:B9999=Overview!E26,IF(OC!I3:I9999OC !
Date(2007,1,1),IF(OC!I3:I9999<OC!Date(2007,6,6),0) ,0))

Basically, range B3:B9999 has to equate to cell OverviewE26, and the
date has to be between Jan1/07 and Jun 6/06

For some reason the formula keeps erroring out on the statement: IF(OC!
I3:I9999

Any ideas? Thanks!

On Oct 25, 12:20 pm, "Chip Pearson" wrote:
What are you trying to do with



If you're looking for date, loose the 'OC' prefix. DATE isn't a cell
reference.

Thanks for the responses guys. The thing I should have mentioned is
that "OC" is actually a sheet name. So essentially with the statement:
IF(OC!I3:I9999<OC!Date(2007,6,6),0) I'm trying to say that "If the
date in column I on sheet OC is less than June 6, 2006.... as a
condition.

Thanks for the responses guys. The thing I should have mentioned is
that "OC" is actually a sheet name. So essentially with the statement:
IF(OC!I3:I9999<OC!Date(2007,6,6),0) I'm trying to say that "If the
date in column I on sheet OC is less than June 6, 2006.... as a
condition.

We know that... but a "date" does not have a sheet reference... it is just a
date (actually, a number). Change your comparisons form this...

OC!I3:I9999<OC!Date(2007,6,6)

to this...

OC!I3:I9999<Date(2007,6,6)

Note there is no 'OC!' in front of the Date function, only in front of the
cell reference.

Rick

We know that... but a "date" does not have a sheet reference... it is just a
date (actually, a number). Change your comparisons form this...

OC!I3:I9999<OC!Date(2007,6,6)

to this...

OC!I3:I9999<Date(2007,6,6)

Note there is no 'OC!' in front of the Date function, only in front of the
cell reference.

Rick

Thank you! I knew it was something like that, but I must have been
staring at it too long :S
Thanks again for the help!

On Oct 25, 4:11 pm, "
wrote:
We know that... but a "date" does not have a sheet reference... it is just a
date (actually, a number). Change your comparisons form this...

OC!I3:I9999<OC!Date(2007,6,6)

Ok - I altered the formula to this:

=SUM(IF('OC''s'!B3:B9999=Overview!E26,IF('OC''s'!
I3:I9999Date(2007,1,1),IF('OC''s'!I3:I9999<(2007, 6,6),0),0))

However the formula is still erroring out, but now I'm not sure why :S