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

質問

はじめまして。

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

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

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

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

回答No.1

MackyNo1 · ベストアンサー

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

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

お礼

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

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

回答No.4

tom04

こんにちは！
すでに色々回答が出ていますので、参考程度で・・・

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

お礼

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

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

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

回答No.3

kybo

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さんのおっしゃる通りですね…！
理由が解ってスッキリしました。
ご丁寧にありがとうございました！

回答No.2

noname#204879

COUNTIFSに固執しない別解です。
セル B8: =SUMPRODUCT(COUNTIF(INDIRECT(A$7),$A$12:$A$19)*($B$12:$B$19=A8))
セル B8 をセル E8 にコピー＆ペースト

お礼

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