エクセルでSUMPRODUCT関数を使って特定の文字を含むものをカウントする

エクセルで数や金額のの集計をする場合、単純な条件であればSUMIFCOUNTIFでいいのですが、複数の条件にマッチする集計の場合はいつもSUMPRODUCT関数を使っています。

SUMPRODUCT関数では"*"などのワイルドカードで特定の文字を指定ができず不便でしかたありません。

ところがいろいろ検索したらSUMPRODUCT関数ワイルドカードを使わなくても特定の文字を含む文字列をカウントする方法があったのでメモ。

ISNUMBERとFIND関数を併用

以下の表を参考にした場合。

特定の文字を含む文字列をカウント

例えば、メモ欄に「リンゴ」が含まれている「セット商品」をカウントする場合の計算式は以下のようになります。

=SUMPRODUCT((B2:B6="セット商品")*(ISNUMBER(FIND("リンゴ",C2:C6))))

特定の文字を含まない文字列をカウント

逆に「リンゴ」が含まれていない「セット商品」をカウントしたい場合はNOT関数を挿入します。

=SUMPRODUCT((B2:B6="セット商品")*(NOT(ISNUMBER(FIND("リンゴ",C2:C6)))))

サンプルファイル

XLSファイルをZIPで圧縮しています。

その他方法

以下の方法でも同じカウントができました。

=COUNT(INDEX(0/SEARCH("セット商品*リンゴ*",B2:B6&C2:C6),))
=COUNT(INDEX(SEARCH("セット商品_*リンゴ*",B2:B6&"_"&C2:C6),))

エクセルは奥が深いですね。