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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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(COFByProjByMonth!I4),ISTEXT('1-TimeExpByProjByMonth'!I4),AND(ISBLANK(DeprnByProjByMonth!I4),ISBLANK('1-TimeExpByProjByMonth'!I4),ISBLANK(COFByProjByMonth!I4)),"",SUM(DeprnByProjByMonth!I4,'1-TimeExpByProjByMonth'!I4,COFByProjByMonth!I4)))
 
So why doesn't that work?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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(COFByProjByMonth!I4),ISTEXT('1-TimeExpByProjByMonth'!I4),AND(ISBLANK(DeprnByProjByMonth!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 said:
So why doesn't that work?

--
HTH

Bob Phillips

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

Dave F said:
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(COFByProjByMonth!I4),ISTEXT('1-T
imeExpByProjByMonth'!I4),AND(ISBLANK(DeprnByProjByMonth!I4),ISBLANK('1-TimeE
xpByProjByMonth'!I4),ISBLANK(COFByProjByMonth!I4)),"",SUM(DeprnByProjByMonth
!I4,'1-TimeExpByProjByMonth'!I4,COFByProjByMonth!I4)))
Click to expand...
Click to expand...
 
Back
Top