Help With Problem: Nested IF Function, Office2K v Office2003

[stevehegg]

Hello Group,

I'm looking for some help with a nested IF worksheet function. Shown
below is a formula that I'm using in a model.

=SUM(IF(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),IF(worksheet!$B$3:$B$54="<90",worksheet!$C$3:$C$54,0),0))

If short, what I'm trying to do is scan the data in two columns and for
any cells which meet the TRUE case in both columns, then sum the third
numbers for all cases returning TRUE-TRUE. If either cell returns a
FALSE, then enter 0.

I created the original formulas and model in Office2000 and have used
the model for more than a year with no problems. Now, I'm trying to
rework the model on a new PC using Office2003. When I try to modify the
formula, I'm getting the #VALUE! error returned.

When I try to troubleshoot using the Evaluate Forumla tool, it returns
a #VALUE! error on the first worksheet look up, as if the forumla is
unable to find the worksheet.

I'm stumped! Anyone have any ideas? Is there a simpler way to do this
formula, such as using VLOOKUP?

 

[Bob Phillips]

It is an array formula, so after adding the formula, you should commit with
Ctrl-Shift-Enter.

It can also be achieved with
=SUMPRODUCT(--(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),--(worksheet!$B$3:
$B$54="<90"),worksheet!$C$3:$C$54,0)

which is NOT an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ron Rosenfeld]

Hello Group,

I'm looking for some help with a nested IF worksheet function. Shown
below is a formula that I'm using in a model.

=SUM(IF(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),IF(worksheet!$B$3:$B$54="<90",worksheet!$C$3:$C$54,0),0))

If short, what I'm trying to do is scan the data in two columns and for
any cells which meet the TRUE case in both columns, then sum the third
numbers for all cases returning TRUE-TRUE. If either cell returns a
FALSE, then enter 0.

I created the original formulas and model in Office2000 and have used
the model for more than a year with no problems. Now, I'm trying to
rework the model on a new PC using Office2003. When I try to modify the
formula, I'm getting the #VALUE! error returned.

When I try to troubleshoot using the Evaluate Forumla tool, it returns
a #VALUE! error on the first worksheet look up, as if the forumla is
unable to find the worksheet.

I'm stumped! Anyone have any ideas? Is there a simpler way to do this
formula, such as using VLOOKUP?

Probably you did not enter your formula as an ARRAY formula (holding down
<ctrl><shift> while hitting <enter>).

You could also write it as the **array** formula:

=IF(AND(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),worksheet!$B$3:$B$54="<90"),worksheet!$C$3:$C$54,0)


--ron

 

[stevehegg]

Probably you did not enter your formula as an ARRAY formula (holding down
<ctrl><shift> while hitting <enter>).

Thanks Bob & Ron .... it's been so long since I originally created the
model, that I forgot about that important step in the formula entry.
That solved the problem. Cheers!