COUNTIFS関数について

質問

お世話になります。

3つ以上の条件を含む式の間違いが分かりません。
=COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!G$1:Q$200,B3)
のようなブック内の別シートを3つの条件に合うデータ件数を集計したいのですが、#value!が出ます。
（ )内の3つの条件をばらして、
=COUNTIFS(sheet1!A$1:A$200,E$1)　(1)
=COUNTIFS(sheet1!C$1:C$200,"2")　(2)
=COUNTIFS(sheet1!G$1:Q$200,B3)　(3)
および
=COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2")
は、正しく計算できるのですが、なぜか(1)～(3)を一つの式にすると、エラーになります。
何が悪いのでしょうか。

回答No.10

kkkkkm · ベストアンサー

> 先ほどの式をもういち度見直したところ、エラーではなく「0」が返ってきて、予想される値と異なるのです。

変だなぁと思っていろいろデータを入れて試したところ、条件に一致する値以上の結果が出ることもあり
=SUMPRODUCT((Sheet1!A$1:A$200=E$1)*(Sheet1!C$1:C$200=2)*(Sheet1!G$1:Q$200=B3))
この式では正しい結果が得られないことがわかりました。最後の複数行指定で駄目みたいです。テスト不足でいらぬ時間を取らせてしまって申し訳ありませんでした。

違うアプローチを考えた方がよさそうですね。

お礼

わざわざデータを作って試していただいたのですね。
ご丁寧な回答ありがとうございます。

今まで2003のときは、第一・第二の式の分は、データのシート全体を並べ替えて、第三の式部分だけをcountif関数で処理していました。
2007からCOUNTIFS関数ができたので、並べ替えなしで一気に処理できるかと思ったのですが、無理なようですね。
本当にありがとうございました。

回答No.11

kkkkkm

No10です。

今更ながらなのですが、よく考えたら

sheet1!A$1がE$1、かつ、sheet1!C$1が2、かつ最後の条件は!sheet1!G$1:Q$1がB3になるので、sheet1!G$1:Q$1でB3が複数列で合致すると、その分複数カウントされてしまいます。条件的にはそれでいいのでしょうか。

たとえば
sheet1!A$1がE$1と合致
sheet1!C$1が2と合致
sheet1!G$1とsheet1!H$1でB3と合致

の場合、結果は2でカウントされるというような感じです。

お礼

すみません、こちらを読まずに閉めきってしまいました。

具体的なデータを出していないのでわかりにくかったかと思います。

sheet1のG:Qには、同じ行には同じ数字は入りません。もしあったとしても（例のsheet1!G$1とsheet1!H$1でB3と合致）、2件とカウントして問題ありません。

ご心配ありがとうございます。

回答No.9

MackyNo1

>ご紹介の式の意味は分かりましたが、その通りに入力してもエラーになります。

提示した数式をそのままコピー貼り付けすれば、少なくともエラーが出ることはありません。

エラーの種類が#VALUE!エラーなら、数式の行範囲（行数）がすべての範囲で一致していないパターンなどが考えられます（もちろん参照範囲にエラー値が含まれている場合もエラー表示されます）。
落ち着いて、数式やデータ範囲に問題がないか調べてみてください。

＞それと…検索値は文字列ではなく数値を検索したかったのでした。

COUNTIF関数では、検索値を文字列と数値のいずれで指定しても、結果として正しい結果が返りますが（文字列と数値の区別がない）、一般的な関数（たとえばVLOOKUP関数など）では数値と文字列数字は別のものと判断されますので注意してください（数値の場合は「""」で囲まない）。

補足

たびたびありがとうございます。

が、先ほどの式をもういち度見直したところ、エラーではなく「0」が返ってきて、予想される値と異なるのです。
フィルタなど使ってデータを目視しても、明らかに条件に合うデータがあるのですが。

回答No.8

kkkkkm

> ご紹介の式の意味は分かりましたが、その通りに入力してもエラーになります。

どのようなエラーでしょう。バラバラに配置したらどうなるでしょう。
=SUMPRODUCT((Sheet1!A$1:A$200=E$1)*1)
=SUMPRODUCT((Sheet1!C$1:C$200=2)*1)
=SUMPRODUCT((Sheet1!G$1:Q$200=B3)*1)

補足

たびたびありがとうございます。
バラバラに配置すると問題ありません。正しい値が返ってきます。

が、先ほどの式をもういち度見直したところ、エラーではなく「0」が返ってきて、予想される値と異なるのです。
フィルタなど使ってデータを目視しても、明らかに条件に合うデータがあるのですが。

回答No.7

bunjii

>何が悪いのでしょうか。
3つの条件で検索条件範囲の大きさが揃っていませんので一致させてください。
COUNTIFS(sheet1!A$1:A$200,E$1)とCOUNTIFS(sheet1!C$1:C$200,"2")は条件範囲が1列でCOUNTIFS(sheet1!G$1:Q$200,B3)のみ11列ありますので数式の整合性が合わないのでしょう。
行数については全てが1から200の200行なので列数を揃えればエラーになりません。
各範囲で対象の行番号が異なっても行数が一致していればエラーになりません。
何故そうなるかは分かりませんがCOUNTIFS関数の仕様なのでしょう。

お礼

ご回答ありがとうございます。
三番目の式は、11列の中からセルB3と合致するものを数えたいので、COUNTIFS関数では無理そうですね。

回答No.6

mshr1962

＞何が悪いのでしょうか。
最後の範囲だけがG列からQ列の複数列（11列）だからです。

=COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!G$1:G$200,B3)
とか
=COUNTIFS(sheet1!A$1:K$200,E$1,sheet1!C$1:M$200,"2",sheet1!G$1:Q$200,B3)
なら正しい答えがでるはずですよ。

どうしてもという場合は
=COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!G$1:G$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!H$1:H$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!I$1:I$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!J$1:J$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!K$1:K$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!L$1:L$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!M$1:M$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!N$1:N$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!O$1:O$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!P$1:P$200,B3)+COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!Q$1:Q$200,B3)
とすれば計算できますが。。。

お礼

ご回答ありがとうございます。
3番目の式に合わせて1・2番目の式の範囲も11列にすると、えられる値が変わってしまいます。
どうしても、という場合…非現実的ですよね(笑）。

回答No.5

keithin

COUNTIFS関数のヘルプに、次のように書かれています。確認してください。

----------------
重要
各追加範囲の列数と行数は "条件範囲 1" 引数と同じである必要があります。範囲どうしは隣接していなくてもかまいません。
----------------

Ａ列と一緒にＧ：Ｑをまとめては計算できないので、もうちょっと計算したい事を整理して、別の考え方を工夫してみて下さい。

お礼

ご回答ありがとうございます。
「"条件範囲 1" 引数と同じ」の意味が分かりませんでしたが、他の方の回答を見てなんとなく理解しました。
3番目の式で計算したいのは、複数列の中からB3と合致するデータ数を出すことです。

回答No.4

High_Score

Countifsでは3つの検索範囲が列数、行数とも同じである必要があります。質問文を見ると、3つめの列数が他と違うためエラーになったのでしょう。

お礼

ご回答ありがとうございます。

回答No.3

MackyNo1

＞=COUNTIFS(sheet1!A$1:A$200,E$1,sheet1!C$1:C$200,"2",sheet1!G$1:Q$200,B3)

COUNTIFS関数の範囲は同じ大きさでなければなりません。

すなわち、最後の範囲が「sheet1!G$1:Q$200」と1列のデータになっていないためです。

G列だけの集計なら「sheet1!G$1:G$200」にすればエラーなく計算できます。

もしご希望の集計条件が、最後の検索条件だけ列範囲が異なる条件で集計したいなら、計算負荷の高い配列数式を使用する必要があります。

=SUMPRODUCT((sheet1!A$1:A$200=E$1)*(sheet1!C$1:C$200="2")*(sheet1!G$1:Q$200=B3))

#気になったので確認ですが、数式では検索値が「"2"」となっていますが、文字列の「2」を検索していて、数値の「2」を検索していないのですね。

補足

ご回答ありがとうございます。
ご紹介の式の意味は分かりましたが、その通りに入力してもエラーになります。

それと…検索値は文字列ではなく数値を検索したかったのでした。こちら http://www.jimcom.co.jp/excel/function02/000137.html
を見たら、直接入力するときは” ”で囲め、とありましたので。誤解だったようです。

回答No.2

kkkkkm

こちらに解説がありますが
http://dekiru.net/article/4514/
•すべての［範囲］は同じ行数、列数を指定する必要があります。

なので最後の範囲が他の範囲と違いますので#value!となります。

対応としては下記の数式でできると思います。
=SUMPRODUCT((Sheet1!A$1:A$200=E$1)*(Sheet1!C$1:C$200=2)*(Sheet1!G$1:Q$200=B3))

補足

ご回答ありがとうございます。
ご紹介の式の意味は分かりましたが、その通りに入力してもエラーになります。

回答No.1

aokii

循環参照では？

補足

ご回答ありがとうございます。
循環参照であるとのエラーはありませんでした。