# FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED

1. ## FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED

=COUNTIF('[4DLWEDNESDAY.XLS]9501'!\$I2:\$U2,\$AJ\$3)-COUNTIF('[4DLWEDNESDAY.XLS]9501'!\$W2:\$AA2,\$AJ\$3)

I WOULD USE AN ARRAY FORMULA OF =COUNT(IF((
BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND
WILL NOT TAKE AN ARRAY FORMULA.

\$AJ\$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST
BILLING CODES)

2. ## Re: FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED

Maybe you could use =sumproduct()

=sumproduct(--('[4DLWEDNESDAY.XLS]9501'!\$I2:\$U2=\$aj\$3))
- sumproduct(--('[4DLWEDNESDAY.XLS]9501'!\$W2:\$AA2=\$aj\$3))

(Untested)

Tomkat743 wrote:
>
> =COUNTIF('[4DLWEDNESDAY.XLS]9501'!\$I2:\$U2,\$AJ\$3)-COUNTIF('[4DLWEDNESDAY.XLS]9501'!\$W2:\$AA2,\$AJ\$3)
>
> I WOULD USE AN ARRAY FORMULA OF =COUNT(IF((
> BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND
> WILL NOT TAKE AN ARRAY FORMULA.
>
> \$AJ\$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST
> BILLING CODES)

--

Dave Peterson

3. ## Re: FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED

Find a way to get rid of the merged cells, they are not worth the problems
that they create.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Tomkat743" <Tomkat743@discussions.microsoft.com> wrote in message
news:375D8F2E-1EAF-42B4-BEFE-8FC783B4E6EB@microsoft.com...
>

=COUNTIF('[4DLWEDNESDAY.XLS]9501'!\$I2:\$U2,\$AJ\$3)-COUNTIF('[4DLWEDNESDAY.XLS]
9501'!\$W2:\$AA2,\$AJ\$3)
>
> I WOULD USE AN ARRAY FORMULA OF =COUNT(IF((
> BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND
> WILL NOT TAKE AN ARRAY FORMULA.
>
> \$AJ\$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST
> BILLING CODES)

4. ## Re: FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED

YOU ARE MY HERO!!!!!!!!!!!! THAT WAS PERFECT.... I WOULD LIKE TO KNOW WHY
BUT AT THIS POINT I'M JUST HAPPY I FOUND SOMETHING THAT WORKED

"Dave Peterson" wrote:

> Maybe you could use =sumproduct()
>
> =sumproduct(--('[4DLWEDNESDAY.XLS]9501'!\$I2:\$U2=\$aj\$3))
> - sumproduct(--('[4DLWEDNESDAY.XLS]9501'!\$W2:\$AA2=\$aj\$3))
>
> (Untested)
>
> Tomkat743 wrote:
> >
> > =COUNTIF('[4DLWEDNESDAY.XLS]9501'!\$I2:\$U2,\$AJ\$3)-COUNTIF('[4DLWEDNESDAY.XLS]9501'!\$W2:\$AA2,\$AJ\$3)
> >
> > I WOULD USE AN ARRAY FORMULA OF =COUNT(IF((
> > BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND
> > WILL NOT TAKE AN ARRAY FORMULA.
> >
> > \$AJ\$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST
> > BILLING CODES)

>
> --
>
> Dave Peterson
>

5. ## Re: FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED

There are some functions that don't work with closed workbooks:

=indirect(), =countif() and =sumif() for example.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

And just like any array formula, you can't use the whole column.

Tomkat743 wrote:
>
> YOU ARE MY HERO!!!!!!!!!!!! THAT WAS PERFECT.... I WOULD LIKE TO KNOW WHY
> BUT AT THIS POINT I'M JUST HAPPY I FOUND SOMETHING THAT WORKED
>
> "Dave Peterson" wrote:
>
> > Maybe you could use =sumproduct()
> >
> > =sumproduct(--('[4DLWEDNESDAY.XLS]9501'!\$I2:\$U2=\$aj\$3))
> > - sumproduct(--('[4DLWEDNESDAY.XLS]9501'!\$W2:\$AA2=\$aj\$3))
> >
> > (Untested)
> >
> > Tomkat743 wrote:
> > >
> > > =COUNTIF('[4DLWEDNESDAY.XLS]9501'!\$I2:\$U2,\$AJ\$3)-COUNTIF('[4DLWEDNESDAY.XLS]9501'!\$W2:\$AA2,\$AJ\$3)
> > >
> > > I WOULD USE AN ARRAY FORMULA OF =COUNT(IF((
> > > BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND
> > > WILL NOT TAKE AN ARRAY FORMULA.
> > >
> > > \$AJ\$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST
> > > BILLING CODES)

> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1