complex IF(OR(...AND())) statement
 

OK, my head's spinning with this one. I'm trying to suppress both errors and
0 values from being returned. In either case, I want an empty string to be
returned.

Here are the conditions:

IF

1) DeprnByProjectByMonth!I4 is text OR
2) COFByProjByMonthI4 is text OR
3) 1-TimeExpByProjByMonth is text OR
4) DeprnByProjectByMonth!I4 is blank AND
5) COFByProjByMonthI4 is blank AND
6) 1-TimeExpByProjByMonth is blank

THEN, "" [the empty string]

ELSE,
SUM(DeprnByProjectByMonth!I4,1-TimeExpByProjByMonth!I4,COFByProjByMonth!I4)

Here's what I have:
=IF(OR(ISTEXT(DeprnByProjByMonth!I4),ISTEXT(COFByP rojByMonth!I4),ISTEXT('1-TimeExpByProjByMonth'!I4),AND(ISBLANK(DeprnByProjB yMonth!I4),ISBLANK('1-TimeExpByProjByMonth'!I4),ISBLANK(COFByProjByMonth !I4)),"",SUM(DeprnByProjByMonth!I4,'1-TimeExpByProjByMonth'!I4,COFByProjByMonth!I4)))
--
Brevity is the soul of wit.

complex IF(OR(...AND())) statement
 

So why doesn't that work?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
OK, my head's spinning with this one. I'm trying to suppress both errors
and
0 values from being returned. In either case, I want an empty string to
be
returned.

Here are the conditions:

IF

1) DeprnByProjectByMonth!I4 is text OR
2) COFByProjByMonthI4 is text OR
3) 1-TimeExpByProjByMonth is text OR
4) DeprnByProjectByMonth!I4 is blank AND
5) COFByProjByMonthI4 is blank AND
6) 1-TimeExpByProjByMonth is blank

THEN, "" [the empty string]

ELSE,

SUM(DeprnByProjectByMonth!I4,1-TimeExpByProjByMonth!I4,COFByProjByMonth!I4)

Here's what I have:

=IF(OR(ISTEXT(DeprnByProjByMonth!I4),ISTEXT(COFByP rojByMonth!I4),ISTEXT('1-T
imeExpByProjByMonth'!I4),AND(ISBLANK(DeprnByProjBy Month!I4),ISBLANK('1-TimeE
xpByProjByMonth'!I4),ISBLANK(COFByProjByMonth!I4)) ,"",SUM(DeprnByProjByMonth
!I4,'1-TimeExpByProjByMonth'!I4,COFByProjByMonth!I4)))
--
Brevity is the soul of wit.

complex IF(OR(...AND())) statement
 

It doesn't work because as I thought, I had my parentheses all screwed up.
This is the proper syntax:

=IF(OR(ISTEXT(DeprnByProjByMonth!I4),ISTEXT(COFByP rojByMonth!I4),ISTEXT('1-TimeExpByProjByMonth'!I4),AND(ISBLANK(DeprnByProjB yMonth!I4),ISBLANK('1-TimeExpByProjByMonth'!I4),ISBLANK(COFByProjByMonth !I4))),"",SUM(DeprnByProjByMonth!I4,'1-TimeExpByProjByMonth'!I4,COFByProjByMonth!I4))

Thanks.

Dave
--
Brevity is the soul of wit.


"Bob Phillips" wrote:

So why doesn't that work?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
OK, my head's spinning with this one. I'm trying to suppress both errors
and
0 values from being returned. In either case, I want an empty string to
be
returned.

Here are the conditions:

IF

1) DeprnByProjectByMonth!I4 is text OR
2) COFByProjByMonthI4 is text OR
3) 1-TimeExpByProjByMonth is text OR
4) DeprnByProjectByMonth!I4 is blank AND
5) COFByProjByMonthI4 is blank AND
6) 1-TimeExpByProjByMonth is blank

THEN, "" [the empty string]

ELSE,

SUM(DeprnByProjectByMonth!I4,1-TimeExpByProjByMonth!I4,COFByProjByMonth!I4)

Here's what I have:

=IF(OR(ISTEXT(DeprnByProjByMonth!I4),ISTEXT(COFByP rojByMonth!I4),ISTEXT('1-T
imeExpByProjByMonth'!I4),AND(ISBLANK(DeprnByProjBy Month!I4),ISBLANK('1-TimeE
xpByProjByMonth'!I4),ISBLANK(COFByProjByMonth!I4)) ,"",SUM(DeprnByProjByMonth
!I4,'1-TimeExpByProjByMonth'!I4,COFByProjByMonth!I4)))
--
Brevity is the soul of wit.