non-functioning multiple Array formulas in a workbook

[Thom]

I have a workbook with used for forecasting. I use a template sheet for each
forecast (all forecast sheets -54- are the same and contain sheet-level
names).

On each sheet I have a formula that counts the number of chargeable
engagements (where the chargeable engagement is indicated by a "C" in the
preceding column) and places the results in a cell with concatenated text.
The 'chargeable designation' column is a named range.

I can get the formula to work on only two of the worksheets. It can be any
two sheets, but it will only work on two sheets maximum. I have tried a
number of work arounds with no success. I sure this is a straight-forward
problem, but I'm unclear where to start. I have a feeling the named range is
creating the problem, but referencing the range itself does not work either.

example: (this is an excel spreadsheet, clearly, I'm not an designer)

| A | B |C D
E
-- |-------------- |--------------- |-----------------------------------
1 | Chg Status | Engagement |
2 | "C" | eBay |
3 | "C" | Yahoo |
4 | "C" | Google |
5 | "A" | Admin time |
6 | "H" | Holiday time |
7 | | |

(is array formula entered with Shift+Control+Enter)
{=CONCATENATE("Chargeable Client Count = ", COUNTIF(Chg.Type,"C"))}

Where the range in column A is named Chg.Type

I've tried changing the named range (chg.Type) to the direct reference
(A2:A6), but all I get in either case is the formula text displayed in the
cell

{=CONCATENATE("Chargeable Client Count = ", COUNTIF(A2:A7,"C"))}

I have a number of other COUNTIF functions in the worksheet that work fine.
It's the array formulas that are not functioning correctly. The workbook is
currently about 7megs in size.

Any assistance would be greatly appreciated.

thom
(e-mail address removed)

 

[Dave Peterson]

First, I don't see a need to use ctrl-shift-enter on this formula.

Second, try formatting the cell as General and then hitting F2 and then Enter.

(It sounds like the cell was formatted as text before you typed in your
formula.)