i have 3 spreadsheets that i have my employees work in. i have 1 spreadsheet
that is an overview (totals) of the above 3.
i need the overview sheet to show me how many A's, B's, C's are in each of
the 3 spreadsheets.
the current formula i have is
=COUNTIF('S:\Collections\PSS Documents\PSS Data Worksheets\Maria\[Maria PSS
Data Worksheet Jan ''08.xls]Existing Patients'!$K$3:$K$90,"A")
i get the error "#value"
now, if i have one of the 3 spreadsheets open, my overview shows the correct
totals, if i close it, the totals go back to #value.

this is driving me nuts. all of the other "countA" formulas i have are
working fine.

thank you

There are some functions that won't work when the sending workbook is closed:
=indirect(), =sumif(), =countif() are a few


You can replace it with an equivalent =sumproduct() formula.
=sumproduct(--('s:\yourpath[workbook.xls]Existing Patients'!$K$3:$K$90="A"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=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

Joe wrote:

i have 3 spreadsheets that i have my employees work in. i have 1 spreadsheet
that is an overview (totals) of the above 3.
i need the overview sheet to show me how many A's, B's, C's are in each of
the 3 spreadsheets.
the current formula i have is
=COUNTIF('S:\Collections\PSS Documents\PSS Data Worksheets\Maria\[Maria PSS
Data Worksheet Jan ''08.xls]Existing Patients'!$K$3:$K$90,"A")
i get the error "#value"
now, if i have one of the 3 spreadsheets open, my overview shows the correct
totals, if i close it, the totals go back to #value.

this is driving me nuts. all of the other "countA" formulas i have are
working fine.

thank you

--

Dave Peterson

Thanks Dave P.
i'm not familiar with sumproduct, but i figured if i do the countif
function on the sending sheet, then i can just do a simple = formula and have
my overview cell just = or mirror the cell with the formula on the sending
sheet.
thanks for the help!

"Dave Peterson" wrote:

There are some functions that won't work when the sending workbook is closed:
=indirect(), =sumif(), =countif() are a few


You can replace it with an equivalent =sumproduct() formula.
=sumproduct(--('s:\yourpath[workbook.xls]Existing Patients'!$K$3:$K$90="A"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=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

Joe wrote:

i have 3 spreadsheets that i have my employees work in. i have 1 spreadsheet
that is an overview (totals) of the above 3.
i need the overview sheet to show me how many A's, B's, C's are in each of
the 3 spreadsheets.
the current formula i have is
=COUNTIF('S:\Collections\PSS Documents\PSS Data Worksheets\Maria\[Maria PSS
Data Worksheet Jan ''08.xls]Existing Patients'!$K$3:$K$90,"A")
i get the error "#value"
now, if i have one of the 3 spreadsheets open, my overview shows the correct
totals, if i close it, the totals go back to #value.

this is driving me nuts. all of the other "countA" formulas i have are
working fine.

thank you

--

Dave Peterson