IFとCOUNTIFSの組み合わせのAND条件が偽になる

Excell2007です。
A1のかごに犬、A4のかごに猫がいます。AND条件をいれました。
①'=IF(COUNTIFS(A1:A1,"犬",A4:A4,"猫"),"います","いません")は、「います」になりますが、
=IF(COUNTIFS(A1:A5,"犬",A1:A5,"猫"),"います","いません")と範囲指定すると、犬も猫も
いなくなってしまいます。
＊犬、猫とも単独捜索では範囲指定でも見つかりました。
②=IF(COUNTIFS(A1:A4,"犬",A1:A5,"猫"),"います","いません")と犬と猫の捜索範囲が異なると
#VALUEになりますが、犬と猫の範囲は一緒じゃないと駄目でしょうか。

No.4 ベストアンサー 回答者： enunokokoro 回答日時： 2022/08/27 22:55

COUNTIFS関数の複数条件を満たす場合の理解が間違い？

①が「います」のTrueになるのは
　=COUNTIFS(A1:A1,"犬",A4:A4,"猫")
の配列が {"犬"} と {"猫"} の同じ1つのセル範囲で満たしている
ためで、同じ高さ内での位置が1つしかないからです。
一方で「いません」のFalseになるのは
　=COUNTIFS(A1:A5,"犬",A1:A5,"猫")
の配列で、{"犬";0;0;0;0} と {0;0;0;"猫";0} となり、同じセル範囲で
あっても、同じ位置に条件を満たすものがないので、AND条件を
満たさないからです。

{"犬";0;0;0;0}
{0;0;0;"猫";0}
だと、1つ目の条件では1番目に犬がありますが、2つ目の条件
の場合では4番目にあるので、同じ位置にはないことから条件を
満たさないのです。

A列のみで条件を満たそうとするなら、①のTrueになるものか、
　=COUNTIFS(A1:A2,"犬",A4:A5,"猫")
　=COUNTIFS(A1:A3,"犬",A4:A6,"猫")
　=COUNTIFS(A1:A4,"犬",A4:A7,"猫")
のように、条件範囲の高さを揃えて、それぞれの範囲の初めを
犬や猫が配列で揃う位置にする必要があるのです。

AND条件を満たすのは、その範囲にあるということではなくて、
(列や行などに関係なく)同じ位置にあることが必要です。

今回の場合はOR条件なので、COUNTIFS関数を使うならば
　=IF(COUNTIFS(A1:A5,"犬")+COUNTIFS(A4:A5,"猫"),"います","いません")
のようにすることで対応します。
https://office-hack.com/excel/countif-multiple-c …
こちら↑の「男+女」を数えることと同じことなので。

ちなみに、COUNTIFS関数を使うよりCOUNTIF関数のほうが、
今回の場合なら適しているかもしれませんね。
　=IF(COUNTIF(A1:A5,"犬")+COUNTIF(A1:A5,"猫"),"います","いません")

この回答へのお礼

回答ありがとうございます。
COUNTIFは複数条件が使えないとあったので、COUNTIFSにしたのですが、
{"犬";0;0;0;0} と {0;0;0;"猫";0}とURLでわかりました。
2つの配列をイメージすると理解できます。
例：猫を別属性の餌に置き換えて、自分のまん前に餌が置いてあると食べられる。隣のかごの前に置いてあると食べられない。表のカウントには使えますが、真偽では使えなさそうです。
+COUNTIFを使います。
丁寧な解説ありがとうございました。

お礼日時：2022/08/28 09:34

No.6 回答者： mike_g 回答日時： 2022/08/28 00:35

[No.2お礼]へのコメント、

》 範囲に読み替えて試したら…
なぜそんなクダラナイことをするの？
貴方は「A1のかごに犬、A4のかごに猫がいます」とワザワザ宣わっていたじゃない？
不必要なことをやるのはやめましょう。

この回答へのお礼

何が気に障ったかわかりませんが、範囲指定の関数の質問をしているのですから「A1のかごに犬、A4のかごに猫がいます」というのは一例に決まってます。A1,A4指定の例はセル直指定なら出来たというサンプルです。
セル直指定で使うならCOUNTIF関数なんかは使いません。
それこそ不要でクダラナイ使い方だと思いますね。
せっかくヒントのお礼したのに、心外なコメントで残念です。

お礼日時：2022/08/28 09:46

No.5 回答者： taglet 回答日時： 2022/08/27 23:03

以下の場合、A1に犬と猫が一緒にいないと、「います」になりません。

そのような状況はあり得ないので、必ず「いません」となります。
=IF(COUNTIFS(A1:A5,"犬",A1:A5,"猫"),"います","いません")

このような式にすると、「います」になります。
=IF(COUNTIFS(A1:A3,"犬",A4:A6,"猫"),"います","いません")

この回答へのお礼

「A1に犬と猫が一緒にいないと、「います」になりません。」
おなじ籠ということですね。
ありがとうございました。

お礼日時：2022/08/28 09:35

No.3 回答者： mike_g 回答日時： 2022/08/27 21:59

あるいは、単に

=IF((A1="犬")*(A4="猫"),"います","いません")
でも、ダメダメですか？

No.2 回答者： mike_g 回答日時： 2022/08/27 21:56

=IF(COUNTIF(A1,"犬")*COUNTIF(A4,"猫"),"います","いません")

では、駄目よダメダメですか？

この回答へのお礼

２つ回答ありがとうございます。
範囲に読み替えて試したら
①=IF(COUNTIF(A1:A5,"犬")*COUNTIF(A1:A5,"猫"),"います","いません")
は、犬も猫も無事かごの中でみつかりました。
②=IF((A1:A5="犬")*(A1:A5="猫"),"います","いません")は、2匹とも
行方不明になっています。

①調べたサイトでORは足し算で、ANDはカンマ繋ぎだったのですが、
掛け算（1*1)が一番しっくり来ますね。

どうしてカンマつなぎが偽になってしまうのかわかりませんが、
掛け算方式のほうが、区切りも見易いしこちらを使います。
ありがとうございました。

お礼日時：2022/08/27 22:17

No.1 回答者： taglet 回答日時： 2022/08/27 21:49

COUNTIFSの範囲は、それぞれ同じ大きさでなければいけません。

そして、それぞれの範囲の位置で条件があっているセルがカウントされます。

例えば、A1、A2に犬がいて、B1とB3に猫がいる場合、
=COUNTIFS(A1:A5,"犬",B1:B5,"猫")は1となります。

参考）COUNTIFS関数の使い方
https://dekiru.net/article/19816/

この回答へのお礼

回答ありがとうございます。
範囲が同じという条件はわかりました。
1点目の質問ですが、であれば、=IF(COUNTIFS(A1:A5,"犬",A1:A5,"猫"),"います","いません")は、「（どちらも）います」になるはずなのですが「いません」になりました。どこがおかしいのでしょうか。

お礼日時：2022/08/27 21:58