「担当者別・月別」の集計表を、COUNTIFSとSUMIFSで作る考え方

売上の明細から「担当者ごと・月ごとの件数と合計」を出したいとき、毎回フィルタをかけて数えていないでしょうか。COUNTIFS と SUMIFS を使うと、明細に行を足すだけで集計表の数字も変わる形にできます。

■ 1. 先に「集計表の形」を決める

縦に担当者、横に月を並べた表を、明細とは別の場所に作ります。ここでは架空の例として、明細の A列に日付、B列に担当者、C列に金額が、2行目から500行目まで入っているとします。集計表は F3 から下に担当者名、G1 から右に各月の初日、G2 から右に各月の末日を入れておきます。

■ 2. 件数は COUNTIFS、合計は SUMIFS

COUNTIFS は「範囲と条件」の組を並べ、すべての条件に合う行を数えます。G3 に入れる件数の数式は次のとおりです。

=COUNTIFS($B$2:$B$500,$F3,$A$2:$A$500,">="&G$1,$A$2:$A$500,"<="&G$2)

担当者が F3 と同じで、日付が月初から月末までの行を数えます。「">="&G$1」のように、比較の記号とセルを & でつなぐと、条件をセルの値で変えられます。

合計にするときは SUMIFS で、合計したい範囲（C列）をいちばん前に置きます。

=SUMIFS($C$2:$C$500,$B$2:$B$500,$F3,$A$2:$A$500,">="&G$1,$A$2:$A$500,"<="&G$2)

SUMIF では合計範囲が3つ目なので、取り違えやすいところです。Googleスプレッドシートの SUMIFS も、合計範囲が先頭です。

■ 3. 「$」の位置で、1つの数式を表全体にコピーする

$F3 は列だけ、G$1 は行だけを固定しています（複合参照）。こうしておくと、G3 の数式を右や下にコピーしても、担当者は F列から、月は1・2行目から読み続けます。明細の範囲は $B$2:$B$500 のように行も列も固定します。Excel では、数式の中の参照を選んで F4 キーを押すと、$ のつけ方を切り替えられます。

■ 4. 範囲の大きさをそろえる

SUMIFS では、合計範囲と条件の範囲の行数・列数がそろっている必要があります。合計範囲だけ C2:C400 のようにずれていると、#VALUE! エラーになります。数字が合わないときは、明細と集計表で担当者名の書き方が同じかも確かめてください。

明細の列の並びや、集計したい切り口（部署別・商品別など）に合わせて数式を作ってほしいときは、下のサービスをご覧ください。再計算で結果を確かめ、どのセルに何を入れたかの説明をつけてお返しします。名前や金額はダミーに置き換えてお送りください。