COUNTIFSで名前の定義を使用する方法

はじめまして。

添付ファイルのような表があり、それぞれＡ班のりんご・みかんそれぞれの合計。Ｂ班のりんご・みかんのそれぞれの合計を出したいのですが、うまくいきません。

名前の定義
A1:A3 … Ａ班
B1:B3 … Ｂ班

B8セルには下記の計算式を入れています。
=COUNTIFS(A12:A19,A班,B12:B19,A8)

解決方法がわかる方、どなたか教えて頂けないでしょうか。
よろしくお願いします。

No.1 ベストアンサー 回答者： MackyNo1 回答日時： 2014/07/28 11:50

提示された数式は、配列数式ですので以下のようにSUMPRODUCTで集計してください。

=SUMPRODUCT(COUNTIFS($A$12:$A$19,A班,$B$12:$B$19,A8))

この回答へのお礼

素早い回答ありがとうございました！

この式で難なくこたえを出すことが出来ました！
今ある表をそのまま利用出来るのと、一番シンプルな式でしたので、ベストアンサーとさせていただきます。

お礼日時：2014/07/28 16:24

No.4 回答者： tom04 回答日時： 2014/07/28 16:19

こんにちは！

すでに色々回答が出ていますので、参考程度で・・・

COUNTIFS関数を使うとすると↓の画像のように作業用の列を設けた方が簡単だと思います。
区分けは2種類（A班・B班）だけだとして
「名前定義」を利用しています。

作業列C2セルに
=IF(COUNTIF(A班,A2),"A班","B班")
という数式を入れフィルハンドルでずぃ～～～！っと下へコピーしておきます。

そしてF2セルに
=COUNTIFS(C:C,E$1,B:B,E2)
H2セルに
=COUNTIFS(C:C,G$1,B:B,G2)
という数式を入れ、それぞれをフィルハンドルで下へコピー！
これで画像のような感じになります。m(_ _)m

この回答へのお礼

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

参照する表は、データをシステムから落として来るため、出来るだけ作業列を追加しないやり方を考えてました！

こちらのやり方も今後の勉強の為に、覚えておこうと思います。
ご丁寧にありがとうございました！

お礼日時：2014/07/28 16:29

No.3 回答者： kybo 回答日時： 2014/07/28 14:46

COUNTIFS関数は、AかつBかつC　の様な条件設定しかできません。

あなたのしたいことは、Aが1～3の何れかで、かつB　という様な条件です。
「かつ」はできるけど、「または」はCOUNTIFS関数できないのです。

名前の定義を使っているから出来ないのでなく、
「=COUNTIFS(A12:A19,A班,B12:B19,A8)」
では、条件に「A班」と複数のセルをしている事が原因です。

COUNTIFSのみで行うとすれば、
=COUNTIFS(A12:A19,"A",B12:B19,A8)+COUNTIFS(A12:A19,"B",B12:B19,A8)+COUNTIFS(A12:A19,"C",B12:B19,A8)

という長い式になります。
（あなたのやりたいことはこいうことです）

せっかくつけた名前を使いたい場合は、
=COUNTIFS(A12:A19,INDEX(A班,1),B12:B19,A8)+COUNTIFS(A12:A19,INDEX(A班,2),B12:B19,A8)+COUNTIFS(A12:A19,INDEX(A班,3),B12:B19,A8)

といった式になります。

皆さんの回答の通りCOUNTIFSにこだわらなければ、以下の様な感じでもできます。
=SUMPRODUCT(((A12:A19="A")+(A12:A19="B")+(A12:A19="C"))*(B12:B19=A8))

この回答へのお礼

なぜ出来ないのかずっと考えて居ましたが、kyboさんのおっしゃる通りですね…！
理由が解ってスッキリしました。
ご丁寧にありがとうございました！

お礼日時：2014/07/28 16:27

No.2 回答者： mike_g 回答日時： 2014/07/28 12:43

COUNTIFSに固執しない別解です。

セル B8: =SUMPRODUCT(COUNTIF(INDIRECT(A$7),$A$12:$A$19)*($B$12:$B$19=A8))
セル B8 をセル E8 にコピー＆ペースト

この回答へのお礼

なるほど！
他のやり方もあるんですね！
勉強の為にこちらの式も参考に、やってみます。
ありがとうございました！

お礼日時：2014/07/28 16:25