別の列に特定のテキストが含まれる場合に合計する

本チュートリアルでは、別の列のセルに特定のテキストまたは部分テキストが含まれている場合に値を合計する方法を解説します。たとえば、以下のデータ範囲において「T-Shirt」というテキストを含む製品の合計金額を取得したい場合、Excel では SUMIF 関数と SUMPRODUCT 関数のどちらもこのタスクに対応できます。

• SUMIF 関数でセルに特定のテキストまたは部分テキストが含まれる場合に値を合計する
• SUMPRODUCT 関数でセルに特定のテキストまたは部分テキストが含まれる場合に値を合計する

SUMIF 関数でセルに特定のテキストまたは部分テキストが含まれる場合に値を合計する

別の列のセルに特定のテキストが含まれる場合、ワイルドカード（*）を使った SUMIF 関数で値を合計できます。一般的な構文は次のとおりです。

ハードコードされたテキストを用いた一般的な数式：

• range：条件を使って評価したいデータの範囲です。
• *text*：値を合計するための条件です。任意の文字数に一致させるには、ワイルドカード「*」をご利用ください。特定のテキストを含むすべての項目に一致させるには、そのテキストを「*」で前後から囲みます。（)テキストとワイルドカードは必ず二重引用符で囲んでください。）
• sum_range：合計したい数値が含まれるセルの範囲です。

セル参照を用いた一般的な数式：

• range：条件を使って評価したいデータの範囲です。
• 「*」&cell&「*」：合計する値の条件です。
  • *：任意の文字数に一致するワイルドカードです。
  • セル：検索対象の特定のテキストを含むセルです。
  • &：この連結演算子（&）は、セル参照とアスタリスクを結合する際に使用されます。
• sum_range：合計したい数値が含まれるセルの範囲です。

関数の基本的な動作を理解したら、必要に応じて以下のいずれかの数式を使用し、Enterキーを押して結果を取得してください。

注記：この SUMIF 関数は大文字と小文字を区別しません。

SUMPRODUCT 関数でセルに特定のテキストまたは部分テキストが含まれる場合に値を合計する

SUMPRODUCT 関数でもワイルドカードを使わずにこの作業を完了できます。その際には、ISNUMBER 関数と SEARCH 関数を SUMPRODUCT 関数内に組み込む必要があります。一般的な構文は次のとおりです。

• sum_range：合計したい数値が含まれるセル範囲です。
• criteria：値を合計するための条件で、セル参照または定義済みの特定のテキストです。
• range：条件を使用して評価したいデータ範囲です。

空白セルに以下のいずれかの数式を入力し、Enterキーを押して結果を表示してください。

この数式の説明：

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(「T-Shirt」,$A$2:$A$12))))

• SEARCH(「T-Shirt」,$A$2:$A$12)：SEARCH 関数は、データ範囲 A2:A12 内で特定のテキスト「T-Shirt」が出現する位置を返します。その結果、次のような配列が得られます：{5；#VALUE！；#VALUE！；7；#VALUE！；7；#VALUE！；#VALUE！；#VALUE！；#VALUE！；7}。
• ISNUMBER(SEARCH(「T-Shirt」,$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7})：この ISNUMBER 関数は、各要素が数値かどうかを判定し、次のような新しい配列を返します：{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}。
• $B$2:$B$12*(ISNUMBER(SEARCH(「T-Shirt」,$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}：この2 つの配列を乗算する際、算術演算によって TRUE と FALSE の値はそれぞれ1 と0 に自動的に変換されます。そのため、乗算結果は次のようになります：{347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1;0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}。
• SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(「T-Shirt」,$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449})：最後に、SUMPRODUCT 関数が配列内のすべての値を合計し、結果として1605 を得ます。

使用される関連関数：

関連記事：