Googleフォームの結果をスプレッドシートで集計するときの計算式で困っています

初めてGoogleフォームでアンケート調査を行います。
テストをしているところで、回答が返ってくるところはできたのですが、今までの集計グラフにまとめるために、スプレッドシートに入れる計算式で悩んでいます。
どなたかご教示いただければ助かります。

内容は変えてあるのでわかりにくいですが
回答は　「はい」「いいえ」「他」とあり、「はい」または「いいえ」でも「他」を選べる
ようになっています。

まず、フォームの回答シートです
Ａ 　　B 　　　C 　　　 D 　　　　 E　　　　 F
1 　　① 　　　➁　　　　 ③ 　　　　 ④ 　 ⑤
2 佐藤 　　はい 　　　はい,他　　 いいえ、他　はい いいえ
3 金井 　　いいえ、他 はい 　　　 いいえ 　 はい いいえ
4 遠藤 　　いいえ、他 いいえ、他　はい 　 いいえ はい
5 太田 　　はい 　　　いいえ　　　いいえ、他 はい いいえ
6 山本 　　いいえ、他 はい いいえ はい いいえ
7 千葉 　　はい 　　　いいえ はい、他 はい、他 はい、他
8 渡辺 　　いいえ、他 はい いいえ いいえ、他 いいえ、他

「フォームの回答シート」に対してSheet2には、
　　Ａ列　　Ｂ列　　　Ｃ列　　D列　　　E列　　　 F列　　　Ｇ列
1　　① 　はい 　　　　いいえ　　　　　　　他　
2　　➁ 　はい 　　　　いいえ　　　　　　　他
3 以下省略

設問①～⑤のそれぞれの行に「はい」の数、「いいえ」の数、「他」の数を返したいと思います。
例えば、Sheet2のセルＣ１に
=countifs('フォームの回答シート'!B1:F8,$A1,'フォームの回答シート'!B1:F8,"*はい*")
と入れると、「3」のはずですが、「0」となります。
エラーにはならないのですが、正しく計算されません。
間違っているのか、あるいは他の計算式の方がよいのか、教えていただけないでしょうか。

宜しくお願いします。

質問者からの補足コメント

• chonamiさん、お付き合いいただきありがとうございます。
  
  列・行がアップしたらぐちゃぐちゃになっていて、申し訳ないです。
  
  回答は自動的に「フォームの回答シート」に入ってきます。
  Sheet2の方に、
  まずは設問①を選択。これがINDEXですよね。
  そのINXEX選択されたのが設問①なら、設問の列の下の範囲指定した中で、
  設問①とMATCHする列のなかで、「はい」なら「はい」の言葉の数をカウントするという計算式になるのでしょうか。
  
  B1が「はい」なので、"*"B1"*"で囲めばよいのですね。
  
  先ほどは「0」しか出なかったのに、
  今度はエラーメッセージが出てしまいました。
  もう少しやってみます。

    補足日時：2021/10/11 15:26

• chonamiさん、ありがとうございます。
  
  実際は、Sheet1に、こんな式を入れています。
  =COUNTIF(INDEX('フォームの回答 1'!$C$1:$O$200,MATCH($A3,'フォームの回答 1'!$C$1:$O$1,0)),"*はい*")
  
  「フォームの回答 1」のシートには、1行目のC1からO1まで設問が続いています。
  そして、回答数は多くて社内で200件くらい
  Sheet2で、設問列はA列。A3の設問と一致する「フォームの回答 1」のC1:O1のセルの列で
  「はい」の言葉が入っているセルを返すという理屈ですよね。
  "*はい*"だといくつか該当するはずなんですが、「0」となります。
  そしとSheet1で、回答で「はい」が入っているセルがB3なんですが、
  "*"&B3"*" とすると、#ERROR!　数式の解析エラーが出てしまいます。

    補足日時：2021/10/11 16:10

• chonamiさん、ありがとうございます。
  
  実際は、Sheet1に、こんな式を入れています。
  =COUNTIF(INDEX('フォームの回答 1'!$C$1:$O$200,MATCH($A3,'フォームの回答 1'!$C$1:$O$1,0)),"*はい*")
  
  「フォームの回答 1」のシートには、1行目のC1からO1まで設問が続いています。
  そして、回答数は多くて社内で200件くらい
  Sheet2で、設問列はA列。A3の設問と一致する「フォームの回答 1」のC1:O1のセルの列で
  「はい」の言葉が入っているセルを返すという理屈ですよね。
  "*はい*"だといくつか該当するはずなんですが、「0」となります。
  そしとSheet1で、回答で「はい」が入っているセルがB3なんですが、
  "*"&B3"*" とすると、#ERROR!　数式の解析エラーが出てしまいます。

    補足日時：2021/10/11 16:40

• chonamiさん、ありがとうございます。
  
  確かに、最初に提示したExcel表に、chonamiさんの計算式を当てはめると、
  正しい数字が返されていました。びっくり!!
  
  実際はもう少し複雑なんですが、やり方は同じなのに、うまくいきません。
  少し考えてみます。
  ごめんなさい。もう少しなんですが。
  
  &で挟むのはどうしてですか？

    補足日時：2021/10/11 20:05

• chonamiさん、いろいろとありがとうございました。
  
  なぜかはわかりませんが、シート名が関係しているように思います。
  「フォーム回答 1」としていましたが、「 1」を消したらきちんと返してくれました。
  
  これでなんとかできそうですか。
  いろいろご指摘いただきありがとうございました。

    補足日時：2021/10/11 21:21

No.4 ベストアンサー 回答者： chonami 回答日時： 2021/10/11 16:23

質問のレイアウトで私の作成した式とご自身が作成した式をよく見比べてみてください。

まずINDEXの第一引数は「データ部分」のみです。項目行は入っていません。
それからMATCHの第二引数を今回は省略しているのでその部分に，が多く入っているはずです。（第二引数は行の指定なので今回は使わない）

>"*"&B3"*"
実際にこれが入っているんですか？それとも転記ミスですか？
これも私の式と見比べてみてください。

まずは質問のレイアウトで私の式がちゃんと反映するかどうかはやってみましたか？その上でどの式の部分がどこを指定しているか確認したでしょうか？

No.3 回答者： chonami 回答日時： 2021/10/11 15:42

>列・行がアップしたらぐちゃぐちゃになっていて、申し訳ないです

いえ。コピペしてスペースで区切ったらちゃんと収まりましたよ。

>"*"B1"*"で囲めばよいのですね。

実際の式は&が入ってますがそちらは間違いないですか？

>今度はエラーメッセージが出てしまいました

エラーは何でしょう？
実際のレイアウトと質問のレイアウトとは違ったということですか？

一応式の説明ですが、INDEXの第一引数は返したいデータの範囲を選択します。
列番号の指定部分で、MATCHで丸番号の部分を選択しそこから1などを探すことで何列目かを指定しています。
例えば1はB1からF1の中で1つ目にあるからMATCHの返り値から列番号が1となりINDEXの範囲の1列目を指定したことになるということです。

その中から部分一致でCOUNTIFを使っています。

多分スプレッドシートでも同じようにできると思いますが。

No.2 回答者： chonami 回答日時： 2021/10/11 14:14

いちいち参照先を変えない式としては

=COUNTIF(INDEX(フォームの回答シート!$B$2:$F$8,,MATCH($A1,フォームの回答シート!$B$1:$F$1,0)),"*"&B1&"*")

これをC1、E1、G1に貼り付けて下にコピーすれば数が出ると思います。
（例示通りのレイアウトならですが）

No.1 回答者： chonami 回答日時： 2021/10/11 13:23

COUNTIFSの範囲指定は1列または1行でないとうまくいかないと思いますよ。

1番の問題のはいとかを数えるなら列は決まっていますから、そこの列だけ指定しては？