検索条件に一致するセルの個数を返す COUNTIF関数と COUNTIFS関数は、EXCELでもよく使われる関数だと思います。それに加え、SUMPRODUCT関数も使うとさらに便利です。
これらの関数について、論理和（OR・または）、論理積（AND・および）、比較演算子、文字列のみ、数値のみ、ワイルドカード（部分一致、後方一致、前方一致、中間一致）など、思いつく条件をまとめてみました。
- COUNTIF(範囲, 検索値)
- 指定した範囲において検索条件に一致するセル（単一条件）の個数を返す。
- COUNTIFS(検索条件範囲1, 検索条件1, …)
- 指定した範囲において検索条件に一致するセル（複数条件）の個数を返す。
- SUMPRODUCT(配列1, [配列2], [配列3], …)
- 対応する範囲または配列の積の合計を返す。既定の操作は乗算ですが、加算、減算、除算も可能。条件に一致するセルの合計を求めることができる。COUNTIF関数の代わりにSUMPRODUCT関数を使用するメリットは、開いていない他ブックを参照してもエラーとならないことです。デメリットは、ワイルドカードを使うことが出来ないことです。
論理積（AND条件）
複数条件で結果を絞ります。
すべての条件を満たす場合のみカウントします。
=COUNTIFS(検索条件範囲1,検索条件1,検索条件範囲2,検索条件2,...) =SUMPRODUCT((検索条件範囲1="検索条件1")*(検索条件範囲2="検索条件2")*...)
論理和（OR条件）
複数条件で結果を広げます。
少なくとも1つの条件を満たせばカウントします。
=COUNTIF(範囲1, 検索値1)+COUNTIF(範囲2, 検索値2)+... =SUMPRODUCT((検索条件範囲1="検索条件1")+(検索条件範囲2="検索条件2")+...)
上記は単純にカウント数を足しているので、検索範囲が同じ配列で単列（行）であれば重複しないカウントを求めることが出来ますが、検索範囲が異なる配列で複数列（行）の場合は重複することがあります。
下記はCOUNTIF関数を使用した例です。黄色セルがカウントされたセルになります。同じ配列（A1:A13）を検索範囲としているので、重複しません。
下記はSUMPRODUCT関数の例です。同様に重複はしません。
検索範囲が異なる配列で複数列（行）の場合の重複分を差し引いて重複しないカウントにする式が下記です。
検索範囲が異なる2つの配列でカウントが重複する場合
=COUNTIF(範囲1, 検索値1)+COUNTIF(範囲2, 検索値2)-COUNTIFS(検索条件範囲1,検索条件1,検索条件範囲2,検索条件2) =SUMPRODUCT((検索条件範囲1="検索条件1")+(検索条件範囲2="検索条件2"))-SUMPRODUCT((検索条件範囲1="検索条件1")*(検索条件範囲2="検索条件2"))
検索範囲が2種類の場合は、重複は論理積で求められますので、その分を差し引きます。
検索範囲が異なる3つ以上の配列でカウントが重複する場合
=COUNTIF(範囲1, 検索値1)+COUNTIF(範囲2, 検索値2)+COUNTIF(範囲3, 検索値3)-COUNTIFS(検索条件範囲1,検索条件1,検索条件範囲2,検索条件2)-COUNTIFS(検索条件範囲1,検索条件1,検索条件範囲3,検索条件3)-COUNTIFS(検索条件範囲2,検索条件2,検索条件範囲3,検索条件3)+COUNTIFS(検索条件範囲1,検索条件1,検索条件範囲2,検索条件2,検索条件範囲3,検索条件3) =SUMPRODUCT((検索条件範囲1="検索条件1")+(検索条件範囲2="検索条件2")+(検索条件範囲3="検索条件3"))-SUMPRODUCT((検索条件範囲1="検索条件1")*(検索条件範囲2="検索条件2"))-SUMPRODUCT((検索条件範囲1="検索条件1")*(検索条件範囲3="検索条件3"))-SUMPRODUCT((検索条件範囲2="検索条件2")*(検索条件範囲3="検索条件3"))+SUMPRODUCT((検索条件範囲1="検索条件1")*(検索条件範囲2="検索条件2")*(検索条件範囲3="検索条件3"))
検索範囲が3種類以上になると、単純に論理積では3条件すべて揃った場合に限られるので、2つずつ論理積を求めて差し引き、差し引きすぎた分に対して、すべての条件を満たす論理積を加えます。
数値セルのみをカウント
検索値を”>=0″とすることで数値セルのみをカウントします。
=COUNTIF(範囲,">=0")
上記は、文字列や数値が混在したA1:C13を範囲として、数値セルのみをカウントしています。
下記のように、比較演算子とCOUNTIF関数などとの組み合わせでカウントする数値の範囲を変えられます。
| = | 等しければTRUE |
|---|---|
| <> | 等しくなければTRUE |
| > | より大きい（超えている）場合にTRUE |
| < | より小さい（未満の）場合にTRUE |
| >= | 以上であればTRUE |
| <= | 以下であればTRUE |
SUMPRODUCT関数では、数値以外はすべて 0 として扱われるので、空白を含む文字列を含まないという意味で、下記のような感じでしょうか。
=SUMPRODUCT((検索条件範囲<"")*1)
SUMPRODUCT関数の条件はTRUE(1)かFALSE(0)を返すだけなので、条件が1つの場合は *1 として 1*1 か 0*1 の結果を返すようにします。複数条件の場合は例えば2条件ですと、TRUE(1)*TRUE(1) か FALSE(0)*FALSE(0) か TRUE(1)*FALSE(0) か FALSE(0)*TRUE(1) になりますので *1 は不要です。
以下、同様にA1:C13を範囲として、検索値やD1セルの式を変化させます。
空白を除く文字列セルをカウント
=COUNTIF(範囲,">!") =SUMPRODUCT((検索条件範囲>"!")*1)
!（エクスクラメーション）は文字コード表で最初に出てくる文字です。検索値を">!"とすることで、空白ではない文字列セルのみをカウントします。
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | A | B | C | D | E | F | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | NUL | SOH | STX | ETX | EOT | ENQ | ACK | BEL | BS | HT | LF | VT | FF | CR | SO | SI |
| 1 | DLE | DC1 | DC2 | DC3 | DC4 | NAK | SYN | ETB | CAN | EM | SUB | ESC | FS | GS | RS | US |
| 2 | SP | ! | " | # | $ | % | & | ' | ( | ) | * | + | , | - | . | / |
| 3 | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | : | ; | < | = | > | ? |
| 4 | @ | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O |
| 5 | P | Q | R | S | T | U | V | W | X | Y | Z | [ | \ | ] | ^ | _ |
| 6 | ` | a | b | c | d | e | f | g | h | i | j | k | l | m | n | o |
| 7 | p | q | r | s | t | u | v | w | x | y | z | { | | | } | ~ | DEL |
あいまい検索
COUNTIF関数はワイルドカード（ *, ? )が使えます。
| *（アスタリスク） | 空文字（""）を含む0文字以上の任意の文字列（未入力のセルは含まない）。 |
|---|---|
| ?（疑問符） | 任意の1文字 |
*（アスタリスク）と?（疑問符）を文字列とする方法
ちなみに * や ? をワイルドカードとしてではなく文字列として扱いたい場合は、前にチルダ( ~ )を付けます。
=COUNTIF(範囲,"~*") =COUNTIF(範囲,"~?")
部分一致
完全一致、前方一致、後方一致、中間一致を含めてカウントします。
=COUNTIF(範囲,"*検索値*")
前方一致、後方一致、中間一致はそれぞれ次のように使えます。
完全一致を含む場合はアスタリスク( * )のみ使い、完全一致を含まない場合は必ず任意の1文字を含むように疑問符( ? )を併用します。
前方一致
=COUNTIF(範囲,"検索値*") =COUNTIF(範囲,"検索値?*")
後方一致
=COUNTIF(範囲,"*検索値") =COUNTIF(範囲,"?*検索値")
中間一致
=COUNTIF(範囲,"?*検索値?*")
SUMPRODUCT関数はワイルドカードが使えないので、FIND関数とISNUMBER関数を使います。
FIND関数で範囲内に検索文字列が含まれる場合に先頭からの文字数（数値）を返し、それをISNUMBER関数で数値かどうかを判断してTRUE(1)を返すことでカウントできます。
ただ、この方法では前方一致や後方一致などは分かりません。とにかく検索値が含まれていればカウントします。COUNTIF関数でいうと検索値の前後にアスタリスク(*)が付いた状態で検索されるのと同じということになります。
=SUMPRODUCT(ISNUMBER(FIND("検索値",範囲))*1)
重複しないデータ件数をカウント
SUMPRODUCT関数と1/COUNTIF関数を使うことで、範囲内の重複しないデータ件数を求めることが出来ます。
=SUMPRODUCT(1/COUNTIF(範囲,範囲))
下記は、COUNTIF関数で COUNTIF(A1:C13,A1) ～ COUNTIF(A1:C13,C13) を総当りし、それぞれのカウント数で1を除算した値の合計が重複しない件数となります。
つまりは、COUNTIF関数では以下のようになり、
=COUNTIF(A1:C13,A1)・・・5 =COUNTIF(A1:C13,A2)・・・5 =COUNTIF(A1:C13,A3)・・・4 : : =COUNTIF(A1:C13,C13)・・・4
その戻り値で1を除算します。
=1/COUNTIF(A1:C13,A1)・・・1/5 =1/COUNTIF(A1:C13,A2)・・・1/5 =1/COUNTIF(A1:C13,A3)・・・1/4 : : =1/COUNTIF(A1:C13,C13)・・・1/4
この時、例えばA1（赤）は5つカウントされていて、1を割ることで1/5個になり、それが全体で5つあるということになるので、SUMPRODUCT関数で合計することで1となり、重複しない件数になります。
以上、ご参考になれば幸いです。
皆さまにすべての良きことが雪崩のごとく起きますように。
コメント