Excel2010 関数 複数条件で人数カウント

質問

Excel2010で、複数条件を指定して人数をカウントする関数を作成したいのですが
うまくできません。やりたい事は、

Sheet1に、1行につき1名の名前（B列）と、○印（C列）が入った表があります。
Sheet2に、2行につき1名の同じ名前（B列）と、○印（C列）が入った表があります。

Sheet2のC列に○印が入った人が何人か知りたいのですが、この時の条件として
Sheet1で○が入っている人で、なおかつSheet2でも○が入っている人、をカウントしたいのです。
添付の図の場合、答えはAさん、Cさん、Jさんの3人となります。

下のような式を作ってみたのですが、Sheet2が2行になっているためダメでした。
=SUMPRODUCT((Sheet1!C2:C11="○")*(Sheet2!C2:C21="○"))

関数でカウントできますでしょうか？
お分かりの方いらっしゃいましたらご教示ください！
よろしくお願いいたします。

回答No.2

KURUMITO · ベストアンサー

SUMPRODUCT関数はデータ数が多くなりますと計算に負担がかかります。
作業列を作って対応します。
シート２ではC列が結合セルの状態で表示されていますが上の行であったり下の行であったり同じ人の場合でも有りうるということなのでその場合には結合セルの状態ではないということになりますね。
また氏名の並びはシート１と２では違っていることも考えられますし、シート２ではデータが無い場合なども考えられますのでシート１の作業列でD２セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(C2="",COUNTIF(Sheet2!B:B,B2)=0),"",IF(OR(INDEX(Sheet2!C:C,MATCH(B2,Sheet2!B:B,0))="○",INDEX(Sheet2!C:C,MATCH(B2,Sheet2!B:B,0)+1)="○"),1,""))

お求めの答えをD１セルに表示させるとしたらD１セルには次の式を入力します。

=SUM(D2:D1000)

お礼

早速回答ありがとうございます。
すごい！出来ました！
私の頭じゃ出来なかったです・・・本当に助かりました。
使わせていただきますね。
どうもありがとうございました！

回答No.1

mu2011

一例です。
Sheet1のD2に=COUNTIFS(Sheet2!B:B,B2,Sheet2!C:C,C2)、下方向にコピー
Sheet1のD列を合計「=SUM(D:D)」で如何でしょうか。

お礼

早々に回答を返していただき、本当に感謝しています！
どうもありがとうございました！

補足

早速回答をありがとうございます！
なるほど、Sheet1の方で考えればいいのですね！目からうろこです。
ただ、申し訳ありません、私の説明が足りていませんでした。
Sheet2のC列の○印は、2行のうち上に入ったり下に入ったり両方入ったりします。
例えばAさんであれば、セルC2もしくはC3のどちらか、又は両方のセルに入力される事があります。
両方のセルに入っていたとしても1名と数えたいのですが・・・
良い方法ありましたら嬉しいのですが・・・もしお付き合い頂けるのであればよろしくお願いします！　＞＜；