Excelでエラーのある行数をカウントしたい（複数列）

EXCEL2003です。
シートに以下のような表があります。

A B C D E
7
8 No. X Y Z Code
9 1 C 10
10 2 A 20
11 3
12 3 A 20
13

１．No.は連番が前提。
　　A列には条件付書式【=AND($A9<>"",$A9<>ROW()-8)】を設定し、
　　A12のような条件では背景色を赤で表示。
２．Codeは省略不可が前提。
　　E列には条件付書式【=AND($A9<>"",$E9="")】を設定し、
　　E11のような条件では背景色を赤で表示。

このとき、エラー件数を求める数式に悩んでいます。
（上記の表の場合であれば、２件（11行目と13行目）と表示）

E列単独であれば
=COUNTIF(INDIRECT(CONCATENATE("$E9:$E",COUNTA($A:$A)+1)),"") 　<- A1～A6には固定データあり
でよいと思いますが、これにA列の判定を加味するとなると・・・？？

まず、A列のエラー件数を求める数式が思い浮かばないのと、
これが求まったとして、
E12がエラーだった場合に2件（3件ではなく！）
とするための方法がわかりません。
エラーセルの背景色（たとえば赤）を数値で求め
カウントする方法（マクロ）はあるようなのですが、
関数で解決する方法はあるでしょうか？

お知恵を拝借できればと思います。
よろしくお願いいたします。

No.4 ベストアンサー 回答者： mt2008 回答日時： 2009/03/19 15:51

もっとすっきりした式になりそうだけどとりあえず……。

=COUNT(A9:A13)-SUMPRODUCT((A9:A13=(ROW(A9:A13)-8))*(A9:A13<>"")*(E9:E13<>""))

この回答へのお礼

ありがとうございます！
早速試したいところなのですが、
派遣が終了して、いまExcelが使えない状況になってしまったため
次の職場が決まってから試してみます。

お礼日時：2009/04/11 11:08

No.3 回答者： n_na_tto 回答日時： 2009/03/19 15:19

何度もすみません。

上の欠点をなくすために、修正しました。
＿＿＿A＿B＿C＿D＿＿_E＿＿＿＿_F_
_8＿No.＿X＿Y＿Z＿Code＿修正個数_
_9＿＿1＿＿_C＿＿＿_10＿＿＿＿_2_
10＿＿2＿A＿＿＿＿＿20＿＿＿＿＿_
11＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿_
12＿＿4＿＿＿＿A＿＿20＿＿＿＿＿_
13＿＿5＿C＿＿＿＿＿30＿＿＿＿＿_
14＿＿6＿＿_A＿＿＿＿＿＿＿＿＿＿

A9
=IF(COUNTA(B9:E9)=0,"",ROW()-8)
下方向・↓
F9
=LOOKUP(10^6,A9:A1000)-COUNTA(E9:E1000)
これにかえてください。

No.2 回答者： n_na_tto 回答日時： 2009/03/19 15:15

追加です。

下のように一番下が未入力のようなときは正しい個数を返しません。
＿＿＿A＿B＿C＿D＿＿_E＿＿＿＿_F_
_8＿No.＿X＿Y＿Z＿Code＿修正個数_
_9＿＿1＿＿_C＿＿＿_10＿＿＿＿_1_
10＿＿2＿A＿＿＿＿＿20＿＿＿＿＿_
11＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿_
12＿＿4＿＿＿＿A＿＿20＿＿＿＿＿_
13＿＿5＿C＿＿＿＿＿30＿＿＿＿＿_
14＿＿＿＿＿A＿＿＿＿＿＿＿＿＿＿

それから、セルの色を取得する方法はありますが、
名前定義を使うなど少し面倒。さらにその方法は、今回のように条件付書式で設定された色の場合は使えないんです。

No.1 回答者： n_na_tto 回答日時： 2009/03/19 15:07

考え方を変えて、E列だけをチェックするのはどうでしょうか？

A列は、【E列に入力されたとき自動で】正しいNoが付くような関数を入れておくのです。

＿＿＿A＿B＿C＿D＿＿_E＿＿＿＿_F_
_8＿No.＿X＿Y＿Z＿Code＿修正個数_
_9＿＿1＿＿_C＿＿＿_10＿＿＿＿_2_
10＿＿2＿A＿＿＿＿＿20＿＿＿＿＿_
11＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿_
12＿＿4＿＿＿＿A＿＿20＿＿＿＿＿_
13＿＿＿_C＿＿＿＿＿＿＿＿＿＿＿_
14＿＿6＿A＿＿＿＿＿30＿＿＿＿＿_

A9
=IF(E9="","",ROW()-8)
下方向・↓
F9
=LOOKUP(10^6,A9:A1000)-COUNTA(E9:E1000)
下方向・↓
※E列のコードが文字列ではなく数値なら
COUNTAのかわりにCOUNTでも。
※10^6はA9:A1000で絶対に現れないめちゃめちゃ大きい数であればほかの数でもOKです。
LOOKUP(10^6,A9:A1000)でA9:A1000の【一番下の数】を返します。
上の例では、6。
そしてCOUNTA(E9:E1000)がE9:E1000で入力されている個数を返します。
上の例では、4。
6-4=2つのセルがE列のコード未入力。

しかしその2つに入力すればA列も自動的に連番が。
＿＿＿A＿B＿C＿D＿＿_E＿＿＿＿_F_
_8＿No.＿X＿Y＿Z＿Code＿修正個数_
_9＿＿1＿＿_C＿＿＿_10＿＿＿＿_0_
10＿＿2＿A＿＿＿＿＿20＿＿＿＿＿_
11＿＿3＿＿＿＿＿＿_40＿＿＿＿＿_
12＿＿4＿＿＿＿A＿＿20＿＿＿＿＿_
13＿＿5＿C＿＿＿＿＿50＿＿＿＿＿_
14＿＿6＿＿＿＿＿＿_30＿＿＿＿＿_

この回答へのお礼

ありがとうございます！
早速試したいところなのですが、
派遣が終了して、いまExcelが使えない状況になってしまったため
次の職場が決まってから試してみます。

お礼日時：2009/04/11 11:07