Function Problem.

[cjwenngatz]

I wrote the following conditional Sum:

=SUM(IF(OC!B3:B9999=Overview!E26,IF(OC!I3:I9999>OC!
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!

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Chip Pearson]

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)

 

[cjwenngatz]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[cjwenngatz]

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!

 

[cjwenngatz]

Ok - I altered the formula to this:

=SUM(IF('OC''s'!B3:B9999=Overview!E26,IF('OC''s'!
I3:I9999>Date(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

 

[Rick Rothstein \(MVP - VB\)]

We know that... but a "date" does not have a sheet reference... it is just

a

Ok - I altered the formula to this:

=SUM(IF('OC''s'!B3:B9999=Overview!E26,IF('OC''s'!
I3:I9999>Date(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'!I3:I9999>DATE(2007,1,1),IF('OC''s'!I3:I9999<DATE(2007,6,6),0),0)))

Rick

 

[Bill Renaud]

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).

 

[cjwenngatz]

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'!I3:I9999>DATE(2007,1,1),IF­('OC''s'!I3:I9999<DATE(2007,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?

 

[Bill Renaud]

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.)