SUMIF関数で『含む・含まない』などの部分一致を検索する方法

ExcelやGoogleスプレッドシートのSUMIF関数で、「～を含む」「～を含まない」などの部分一致を検索する方法をご紹介します。ワイルドカードを使った条件を、複数指定する方法についても解説しているので、ぜひ参考にしてみてください。

SUMIF関数にワイルドカードを使う方法

SUMIF関数で「部分一致」を含むセルを合計したいときに、ワイルドカードと呼ばれる特殊な記号を使うと便利です。ワイルドカードには、主に以下の2種類があります。

特定の文字列を含むセルを合計したい場合、指定する文字列の前後に*を付けます。

たとえば、A列のテキストが「PDF」を含むセルのB列を合計したい場合、数式は以下のようになります。

例：=SUMIF(A2:A5,"*PDF*",B2:B5)

特定の文字列を含まないセルを合計したい場合、指定する文字列の前後に*を付け、さらにその前に否定を表す<>を追加します。

たとえば、A列のテキストが「PDF」を含まないセルのB列を合計したい場合、数式は以下のようになります。

例：=SUMIF(A2:A5,"<>*PDF*",B2:B5)

特定の文字列で始まるセルを合計したい場合、指定する文字列の後に*を付けます。

たとえば、A列のテキストが「PDF」で始まるセルのB列を合計したい場合、数式は以下のようになります。

例：=SUMIF(A2:A5,"PDF*",B2:B5)

特定の文字列で終わるセルを合計したい場合、指定する文字列の前に*を付けます。

たとえば、A列のテキストが「PDF」で終わるセルのB列を合計したい場合、数式は以下のようになります。

例：=SUMIF(A2:A5,"*PDF",B2:B5)

特定の文字をセル参照にしたい場合、ワイルドカードとセル参照を&で結合します。

たとえば、A列のテキストから「A2セルのテキスト」を含むセルのB列を合計したい場合、数式は以下のようになります。

例：=SUMIF(A2:A5,"*"&A2&"*",B2:B5)

ワイルドカードを使用した複数の文字列を、すべて含むセルを合計したい場合、条件を複数指定できるSUMIFS関数を使用します。

たとえば、A列のテキストから「File」と「PDF」の両方を含むセルのB列を合計したい場合、数式は以下のようになります。

例：=SUMIFS(B5:B8,A5:A8,"*File*",A5:A8,"*PDF*")

ワイルドカードを使用した複数の文字列のうち、いずれか一つでも含むセルを合計したい場合、SEARCH関数、ISNUMBER関数、IF関数、SUM関数を組み合わせます。

たとえば、A列のテキストから「File」または「com」を含むセルのB列を合計したい場合、数式は以下のようになります

例：=SUM(IF(ISNUMBER(SEARCH("File",A5:A8))+ISNUMBER(SEARCH("com",A5:A8))>0,B5:B8,0))

※この数式は配列数式であるため、入力後に Ctrl + Shift + Enter キーを押して確定する必要があります。 確定すると、数式全体が自動的に {= ... } のように波括弧で囲まれます。

※Microsoft 365のExcelやExcel 2021など、動的配列に対応したバージョンをお使いの場合は、Ctrl + Shift + Enter は不要で、Enterキーのみで確定できます。

まずSEARCH関数で、セル内に指定した文字列が「最初に現れる位置」を取得します。もし見つかればその位置を数字で返し、見つからなければエラーになります。

次にISNUMBER関数で、SEARCH関数の結果が数字かどうか（文字列が見つかったかどうか）を判定します。見つかればTRUE、見つからなければFALSEを返します。

そして、複数の条件を+（プラス）でつなぐことで『いずれかの条件を満たす』というOR条件を表現します。例えば、{1;1;0;1}+{0;0;0;1}={1;1;0;1}のように、TRUE（1）とFALSE（0）の配列が足し合わされます。

最後にIF関数で、この足し算の結果が0より大きい場合（いずれかの条件を満たしたセル）はB列の値に、そうでない場合は0に変換し、SUM関数で合計します。

ワイルドカードを使用した複数の文字列のうち、いずれか一つでも含むセルを合計したい場合、FILTER関数とREGEXMATCH関数を組み合わせます。

たとえば、A列のテキストから「File」または「com」を含むセルのB列を合計したい場合、数式は以下のようになります

例：=ArrayFormula(SUM(FILTER(B5:B8,REGEXMATCH(A5:A8,"File|com"))))

この数式では、まずREGEXMATCH関数で、テキストが正規表現に一致するかどうかを判定します。|（パイプ）は「または」を表し、「File」か「com」のいずれか一致すればTRUE、一致しなければFALSEを返します。

次に、FILTER関数で、REGEXMATCH関数の結果がTRUEとなった行のB列の値を抽出し、SUM関数で合計します。

REGEXMATCH関数の引数：(テキスト, 正規表現)