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)

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

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" wrote in message
...

=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)

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

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