エクセルの関数式（二つの条件にあうセルの個数をカウント）

前提として二つのシート（XシートとYシート）があります。
Xシートには　A列に　A1、A2、A3、A4…という項目名が入っているとします。
(A1、A2、A3、A4…という項目名はXシートのA列には一度しか入力されていません。）

次にYシートには以下のようになっているとします。
K列に A1、A2、A3、A4 …という項目名が入力されておりますが、Xシートとは異なり、複数回入力されています。（例えば、K列を見ると A1,A3,A2,A4,A4,A4,A3）といった感じ。
そしてYシートのL列に、さらに別の文字列が入っています。例えばですが、犬 , 犬と猫 , 猫, 犬、鳥と猫, みたいな感じ。

K列とL列は横並びであり、例の場合、　A1-犬　、　A3-犬と猫　、　A2-猫　、A4-犬　、A4-鳥と猫
といった感じになります。
この時、XシートのB列に　YシートのK列にA1をL列に犬を含んだセルが何回登場しているのかをカウントしたいのですが、方法が思いつきません。

例えば　＝countif( YシートのK列：L列、XシートのA列）と XシートのB列に入力したら、A列のそれぞれの行に入力されている項目名の登場回数はカウントできると思いますが、L列のことを考慮できません。

有識者の方、いらっしゃいましたら何卒、よろしくお願いします。

No.4 回答者： goomania 回答日時： 2021/08/23 17:06

ご質問者がいう

>YシートのK列にA1をL列に犬を含んだセルが何回登場しているのか
という意味は
「K列のある行に『A1』があるとき、同じ行のL列が『犬』を含んでいるケース」をカウントする･･･(1)
という意味でよろしいでしょうか?
日本語は難しいので、No.2さんの解釈だと、
「K列のある行に『A1』を含んでいるケース、またはL列のある行に『犬』を含んでいるケース」をすべてカウントする･･･(2)
という意味に解釈されています。
ここでは(1)の意味の数式を作成する前提で述べます。
添付画像をご覧ください。既に、No.1さん、No.3さんがお示しになっておられますが、このようなケースで最も標準的に使用される関数はCOUNTIFS関数です。
その理由は「～を含む」という条件のとき「*(ワイルドカード)」が使えるからです。ワイルドカードの詳細については以下のURLをご覧ください。↓
https://qiita.com/aqril_1132/items/9bf905bbda718 …
添付画像で言えば、B1に
=COUNTIFS(Yシート!$K:$K,A1,Yシート!$L:$L,"*犬*")
という数式を記述し、下方向へコピーしています。
この数式と全く同じ意味をもつ数式をSUMPRODUCT関数でも作成できます。ただし、「～を含む」という条件にワイルドカードが使えないので工夫が必要です。それが添付画像のC1に記述している
=SUMPRODUCT((Yシート!$K:$K=A1)*ISNUMBER(FIND("犬",Yシート!$L:$L)))
という数式です。これを下方向へコピーしています。

ご質問の本質とは全く関係ない余談ですが、
ご質問者は「項目名」を「A1、A2、A3、A4･･･」と説明しておられます。
EXCELではA1、A2、A3･･･という「セル名」が実在するので混乱を招きます。
実際のEXCELブックを見ていない回答者に勘違いを誘発させる虞があります。
EXCELのセル名と重複するような項目名の例示は、できれば避けたほうがよいように思います。

No.3 回答者： mike_g 回答日時： 2021/08/23 07:09

Xシートにおいて、式

=COUNTIFS(Y!$K:$K,A1,Y!$L:$L,"*犬*")
を入力したセル A1 を下方にズズーッとオートフィル

No.2 回答者： googoo900 回答日時： 2021/08/23 00:52

作業列をつくってやるのが理解しやすいです。

空白列を作業列として使用し、やり方は以下のとおりです。
※1　ここではM、N列を作業列とします。
※2　シート名、セル範囲は実際のデータにあわせて修正ください。

M1セルに『=COUNTIF(YシートのK1,"*A1*")』　
N1セルに『=COUNTIF(YシートのL1,"*犬*")』
と入力して下方向にデータ下端までコピペします。
M列をSUMで合計したら『A1』の登場回数になり、N列をSUMで合計したら『犬』の登場回数になります。


質問文からやりたいことがはっきり把握できないのですが、M列とN列をSUMで合計したものが出したい答えではないでしょうか。
ちがっていてもここまできたら自力で出せると思います。

No.1 回答者： fujillin 回答日時： 2021/08/22 16:37

こんにちは

COUNTIFをご存じなら、今回は条件が複数あるので、COUNTIFS関数を利用してみてください。
https://support.microsoft.com/ja-jp/office/count …