エクセル関数：検索条件をあいまいにしたい。

質問

よろしくお願いします。

Ａの表は、

　月日　　　　電話番号　　　　　　　　通話料金
１月１日　０１０－３３３－３３３３　　　１００
１月５日　０２０－９９９－９９９９　　　５００
２月３日　０１０－３３３－３３３３　　　３００
２月３日　３４３４－３４３４　　　　　　　２００　・・・・・・

という具合に、電話をした相手の番号と金額が「日付ごと」に
電話番号が「ハイフンつき」で入っていたり、
「市外局番なし」で入っているデータがあります。

一方、Ｂの表には、

短縮番号　　　　　電話番号　　　　　　　　名前　　　　　合計金額
１１１　　　０１０３３３３３３３　　　　おしえて花子　　　　　　？
１１２　　　０２０９９９９９９９　　　　質問太郎　　　　　　　　 ？
１１３　　　０３３４３４３４３４　　　　安心会社　　　　　　　 　？

というように、短縮番号別に　電話番号が「ハイフンなし」で入っている表があるとします。

Ａの表から該当の電話番号を検索し、通話料金の合計をＢの表の「？」部分に返したく、
ＳＵＭＩＦ関数を使用したいと思ったのですが、
ＡとＢの検索条件である「電話番号」がハイフンが入っているとか、市外局番が入っていないとか、
とにかく「完全一致していない」という理由で、うまくできません。

完全一致ではなくても（ハイフンや、市外局番には関係なくて）、検索条件に引っかかり、合計金額が出せるようにするには、どうしたらよいのでしょうか？
たとえば、後ろ６ケタくらいの数字が一致していれば、該当番号になれるようにできれば、よいのですが・・・

よろしくお願いします。

回答No.2

mu2011 · ベストアンサー

NO1です。
>安易に二つの関数を、「+」と「＆」でつなげてみた
⇒安易で問題ありませんが、「＆」は文字列結合ですから意味合いが違いますので「＋」を使用して下さい。
=SUMPRODUCT((RIGHT(SUBSTITUTE(Sheet1!$B$2:$B$100,"－",""),6)=RIGHT(B2,6))*(Sheet1!$C$2:$C$100))+SUMPRODUCT((RIGHT(SUBSTITUTE(Sheet2!$B$2:$B$100,"－",""),6)=RIGHT(B2,6))*(Sheet2!$C$2:$C$100))

お礼

もう一度やってみました。
先ほど、「+」でやってみてできなかったので、
再度お伺いしたのですが、
「+」で合っているとのお返事を頂いたので、もう一度挑戦してみました。
・・・結果、できました。（当たり前ですよね。）

なにが間違えていたのでしょうか？（お恥ずかしいです、申し訳ありませんでした。）

ありがとうございました。

補足

今頃のポイントになってしまい、大変申し訳ありませんでした。
丁寧に回答してくださり、本当にありがとうございました。

回答No.1

mu2011

次の数式は如何でしょうか。
Ａ表をSheet1、セル範囲はA1:C100としています。
=SUMPRODUCT((RIGHT(SUBSTITUTE(sheet1!$B$2:$B$100,"－",""),6)=RIGHT(B2,6))*(sheet1!$C$2:$C$100))

お礼

ありがとうございます！　できました～！

そして、もう一つ。　教えてください。
シートは実は、３つあるんです。

Ａ表が
シート１には「一般電話番号」
シート２には「携帯電話番号」　という具合に
別々の２つのシートに分かれています。

ですから、シート１
=SUMPRODUCT((RIGHT(SUBSTITUTE(sheet1!$B$2:$B$100,"－",""),6)=RIGHT(B2,6))*(sheet1!$C$2:$C$100))

と、シート２
=SUMPRODUCT((RIGHT(SUBSTITUTE(sheet2!$B$2:$B$100,"－",""),6)=RIGHT(B2,6))*(sheet2!$C$2:$C$100))

をシート３にある、Ｂの表に返すには、どうしたらよいのでしょうか？

この２つの関数を一つにつなげればよいのですが、
安易に二つの関数を、「+」と「＆」でつなげてみたのですが、
間違っているようです。

よろしくおねがいします。