Excel セル番地を動的に生成し参照する例

環境

数式 F2

=SUMPRODUCT((YEAR(INDIRECT("$A$2:$A$" & COUNTA($A$2:$A$5000) + 1))=YEAR(E2))
           *(MONTH(INDIRECT("$A$2:$A$" & COUNTA($A$2:$A$5000)+ 1))=MONTH(E2))
           *(INDIRECT("$B$2:$B$" & COUNTA($B$2:$B$5000) + 1) = "外食")
           , INDIRECT("$C$2:$C$" & COUNTA($C$2:$C$5000) + 1))

SUMPRODUCT内でYEAR、MONTH、INDIRECTを使い2022年1月の外食の合計を算出。その際、COUNTA(\$A\$2:\$A\$5000) + 1で最終行の番号を取得し(COUNTAは任意のセル範囲に入力されてるデータの個数を返す関数、その性質を利用しこの例では8を取得、1を加えて最終行の番号9を得る)、INDIRECT("\$A\$2:\$A\$" & COUNTA(\$A\$2:\$A\$5000) + 1で動的に番地を生成(\$A\$2:\$A\$9)・参照。

データ数が増えてくると計算処理中にExcelが重くなる可能性あり。

ワークシート関数[INDIRECT 関数] - Office TANAKA
http://officetanaka.net/excel/function/function/indirect.htm