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

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

You removed the word DATE from the last comparison. The last part of your
formula above reads...

.....IF('OC''s'!I3:I9999<(2007,6,6),0),0))

whereas I think it should read...

.....IF('OC''s'!I3:I9999<DATE(2007,6,6),0),0))

Plus, you are also missing a closing parenthesis at the end of your formula.
And, while it may be correct, you added an apostrophe-s to the OC sheet
names which weren't in your previous postings. Assuming the extra
apostrophe-s on the sheet names is correct, give this formula a try...

=SUM(IF('OC''s'!B3:B9999=Overview!E26,IF('OC''s'!I 3:I9999DATE(2007,1,1),IF('OC''s'!I3:I9999<DATE(20 07,6,6),0),0)))

Rick

I don't understand what this part of the formula is supposed to do:

'OC''s'!B3:B9999=Overview!E26

To me, this says: Check that the range B3 to B9999 on worksheet "OC's" is
equal to the value in cell E26 on worksheet "Overview".

You cannot compare a range of cells with a single cell (or date or any
other single value, for that matter), as far as I know (at least in Excel
2000). Does this mean that each cell in the range has to be equal to the
other cell (in Excel 2007)?

Try entering a simpler formula in a cell:
=('OC''s'!B3:B9999=Overview!E26)

This should return TRUE, if the range B3:B9999 is equal to E26 (on their
respective worksheets). It produces the #VALUE! error for me (Excel 2000).

--
Regards,
Bill Renaud

Plus, you are also missing a closing parenthesis at the end of your formula.
And, while it may be correct, you added an apostrophe-s to the OC sheet
names which weren't in your previous postings. Assuming the extra
apostrophe-s on the sheet names is correct, give this formula a try...

=SUM(IF('OC''s'!B3:B9999=Overview!E26,IF('OC''s'!I 3:I9999DATE(2007,1,1),IF*('OC''s'!I3:I9999<DATE(2 007,6,6),0),0)))

Rick

Ok, that works for me, thanks Rick. For some reason I'm getting Zero
as the outcome to that sum, even though that is not the correct
answer. I'll keep working through it and see what I can find.

Also, In regards to your question Bill, the statement: 'OC''s'!
B3:B9999=Overview!E26
That is essentially stating that as one of the conditions, the number
in column B3:B9999 has to match whatever number I place into cell E26
on the sheet called "Overview". That way I can essentially use that
cell to search for a specific store number, and and all the info comes
up through these forumulas. Is that possible?

I still don't understand your logic. I think you need to start again from
scratch with your problem statement.

Is it something like this:

If (SUM(B3:B999) equals OverviewE26) AND (SomeCell Jun 6, 2006) AND
(SomeCell < Jan 1, 2007), then ResultX, else ResultY.

1. What cell is SomeCell?
2. What is the formula (or value) for ResultX?
3. What is the formula (or value) for ResultY?

(Aside: This is probably NOT a VBA programming question, so should be
posted in the Worksheet.Functions newsgroup.)
--
Regards,
Bill Renaud