EXCEL – SUBTOTAL関数とLOOKUP関数で複数の小計と合計に対応するコピペ計算式

複数の合計行（中計）にも対応する

画像は同様の表をG列とH列に作ったものです。
10行目と20行目に「合計（中計）」を入れています。現状の計算式では、G列セルに「合計」が入力されている場合には、単純に1行目から合計するようになっているので、20行目の「合計」が総合計となっています。ここを、11行目からの合計値（13行目と19行目の小計の計）にしたいところです。
それで、計算式に次のような考え方を追加します。

• 「合計」行セルであれば、当該セルより上の直近の「合計」行セルから当該セル直上セルまでを集計する。
• 「小計」行セルであれば、当該セルより上の直近の「合計」行セルと「小計」行セルのうち、より直近（下方）にあるセルから当該セル直上セルまでを集計する。

すると以下のような式になります。

=IF(G20="合計",SUBTOTAL(9,INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),ROW(H$1:H19))):H19),
IF(LOOKUP(1,0/(G$1:G19="合計"),ROW(H$1:H19))>LOOKUP(1,0/(G$1:G19="小計"),ROW(H$1:H19)),SUBTOTAL(9,INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),ROW(H$1:H19))):H19),SUBTOTAL(9,INDEX(H:H,LOOKUP(1,0/(G$1:G19="小計"),ROW(H$1:H19))):H19)))

これに、さらに以下のエラー処理を追加します。

• 当該セルより上に「合計」行が無い場合に1行目から集計する。
• 当該セルより上の直近の「合計」行セルと「小計」行セルの位置の比較において、「合計」行セルでエラーになる場合（該当するセルが無い場合）に 0 を返し、「小計」行セルでエラーになる場合（該当するセルが無い場合）に 1 を返して比較を成立させる。
• さらに、当該セルより上に「小計」行セルが無い場合に1行目から集計する。

ISERROR()関数でエラー処理しているので長くなってしまいましたが完成形です。
この式は、セルを小計セルと合計セルにコピペして使うことができます。

=IF(G20="合計",SUBTOTAL(9,IF(ISERROR(INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))),H$1,INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))):H19),IF(IF(ISERROR(LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19))),0,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))>IF(ISERROR(LOOKUP(1,0/(G$1:G19="小計"),
ROW(H$1:H19))),1,LOOKUP(1,0/(G$1:G19="小計"),
ROW(H$1:H19))),SUBTOTAL(9,IF(ISERROR(INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))),H$1,INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))):H19),SUBTOTAL(9,IF(ISERROR(INDEX(H:H,LOOKUP(1,0/(G$1:G19="小計"),
ROW(H$1:H19)))),H$1,INDEX(H:H,LOOKUP(1,0/(G$1:G19="小計"),ROW(H$1:H19)))):H19)))

以上、何かのご参考になれば幸いです。