Excel関数のみを使用して複数範囲のSUMIFを拡張性のある数式に置き換えたい

Question

前提

Excelを使用した以下のような担当者別の商品仕入数管理シートがあります。
D2、H2、M2に入っているA、B、Cがそれぞれ担当者名です。
担当者ごとに各商品の仕入をするのですが、仕入先が４つに分かれており、
どこの仕入先から仕入れたかをF列、J列、N列にそれぞれ入力するようになっています。

実現したいこと

上記のエクセル画像のうち、右の方で赤枠で囲んでいる「仕入先別購入数合計」を関数で算出したいです。
今も関数で算出しているのですが、例えばQ5であれば以下のような関数を入れて合計を出しています。

=SUMIF($F5,Q$4,$D5)+SUMIF($J5,Q$4,$H5)+SUMIF($N5,Q$4,$L5)

ただ、実際には担当者の数が50名ほどいるため、SUMIFを50回繰り返して数式を完成させるのではなく
よりシンプルな数式で合計数を出せればと思っているのですが、良い方法に考え至っておりません。

現時点思いついている方法としては、
１．「仕入先」が検索値の文字列と一致していれば
２．OFFSETを使用し「仕入先」列から見て列数が-2のセルの値を合計する
上記の方法で仕入先別購入数合計を出せるのではと思っているのですが、
「仕入先」列の検索範囲が複数列に分かれている点をうまく関数に落とし込めないでいます。

SUMIFを50回繰り返すのではなく、別の方法で合計を出す方法をアドバイスいただけませんでしょうか。

その他

・今回はエクセルを使用する環境上マクロ、ピボットテーブルは使用できません。
・一発で仕入先別購入数合計を出す必要はなく、別の場所に作業用テーブルを準備することはできます。

ご教示のほどどうぞお願いいたします。

Answer 1

多分これでいけると思います。

=SUMIFS($D6:$N6,$D$4:$N$4,"個",OFFSET($D6:$N6,0,2),Q$4)

Comments

[agawa]

アドバイスありがとうございます。まさにこの方法でやりたいことが実現できました。
OFFSETを使用すれば良いのではとは想像しておりましたが他の関数とどのように組み合わせればよいか悩んでいたので、大変助かりました。
検証したところ問題なく集計できましたのでこの方法で進めたいと思います。
本当にありがとうございました。

Answer 2

非表示の「担当者別仕入先別購入数合計」列を追加してみました。

=SUMIF($D4,E$3,$C4)

=E4+K4

担当者および仕入先が追加された場合のメンテナンスが面倒ですが、関数を50回書くよりはシンプルかな？と思います。
いかがでしょうか。

Comments

[agawa]

アドバイスいただきありがとうございます。SUMIF部分を分離してしまうやり方ですね。
やはり「=E4+K4…」と50回足し算を繰り返さないといけないですが、確かにSUMIFを50回足し続けるよりはシンプルになります。
参考にさせていただきます。