Help With Problem: Nested IF Function, Office2K v Office2003

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?

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)

<[email protected]> wrote in message
news:[email protected]...
> 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?
>

On 11 Dec 2005 07:28:43 -0800, [email protected] wrote:

>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

Ron Rosenfeld wrote:

> 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!