スプレッドシートを使ってデータを集計する際、特定の期間に該当するデータを計算したい場面があります。
その際に便利な関数としてSUMIFS関数があります。
ただ残念ながらARRAYFORMULA関数内でSUMIFS関数を使うことはできません。
COUNTIFS関数も使えない！って他のブログを見て思っていたのですが、こっちは使えました！！
本記事では、その制限について解説した上で、代替手法として利用できる『ARRAYFORMULA(SUMIF)』の使い方を詳しく紹介します。
｢SUMIFS｣関数の制限
SUMIFS関数は、特定の条件に一致するセルの合計値や個数を求める際に便利な関数ですが、ARRAYFORMULA関数内では使用できません。
例えば以下の通り数式を入力してみます。
=ARRAYFORMULA(SUMIFS(E2:E,B2:B,H2:H))
Arrayformulaでは「りんご」しか合計が計算されませんでした。
「SUMIF」関数は使える！！
色々試してみたのですが、｢ARRAYFORMULA｣と｢SUMIF関数（単数条件）｣を組み合わせることは可能でした。
以下のような関数式を使います。
=ARRAYFORMULA(SUMIF(B2:B19,H2:H7,E2:E19))
これで「りんご以外」の商品の合計金額も計算出来ました。
SUMIF関数で結合した文字列を使う
｢SUMIFS｣関数と同じように複数条件を使うためには、文字列を結合する方法があります。
例えば、商品と店舗の条件を組み合わせる場合、以下のような関数式を利用します。
=ARRAYFORMULA(SUMIF(B2:B19&C2:C19,G2:G7&H2:H7,D2:D19))
=ARRAYFORMULA(SUMIF(B2:B19&C2:C19,G2:G7&H2:H7,D2:D19))
この方法を使うことで、特定の商品かつ特定の店舗に該当するセルの合計値を求めることができます。
COUNTIFS関数との組み合わせは可能！！
COUNTIFS関数とARRAYFORMULA関数を組み合わせることは可能です。
以下の関数式を使用します。
=ARRAYFORMULA(COUNTIFS(B2:B19,G2:G7,C2:C19,H2:H7))
=ARRAYFORMULA(COUNTIFS(B2:B19,G2:G7,C2:C19,H2:H7))
COUNTIFS関数とARRAYFORMULA関数の組み合わせはできました。
他のブログでは出来ないから他の関数を組み合わせていたのですが、知らないうちにアップデートでもあったのでしょうか・・・。
SUMIFS関数も対応してほしいですね･･････。
大小記号『<』は出来ない？
SUMIFS関数で大小記号『<』を使いたいときがあると思います。
例えば指定した期間の合計を算出したいときなどです。
月ごとの集計ぐらいなら出来るかもと思って以下の方法を試して見まいた。
【式①】E2に入れる式
=ARRAYFORMULA(if(A2:A=””,,text(A2:A,”yymm”)))
【式②】I12に入れる式
=ARRAYFORMULA(SUMIF(B2:B19&C2:C19&E2:E19,G12:G16&H12:H16&$I$11,D2:D19))
=ARRAYFORMULA(if(A2:A="",,text(A2:A,"yymm")))=ARRAYFORMULA(SUMIF(B2:B19&C2:C19&E2:E19,G12:G16&H12:H16&I11,D2:D19))
まずArrayformulaとText関数を使って日付を文字列に変換。ここで｢年と月｣だけ変換算出します。
それに一致するものを合計してみました。
列が増えてしまいますが、工夫次第で計算出来そうです。
スプレッドシート無料公開中
スプレッドシートを見たい方はこちらかどうぞ。無料で公開しています。
スプレッドシートのコピー方法はこちらをご覧ください↓
まとめ
スプレッドシートで期間指定の計算を行う際に使えないSUMIFS関数の代替手法として、ARRAYFORMULA(SUMIF)関数の活用方法を紹介しました。
半年ぐらいもやもやしていたので、少しスッキリしました。
月ごとの合計も、行を追加してあげることで計算出来そうです。
できればもっと細かい日付を選択出来るようにしたいですね･･････。
最新のExcelだとスピルが使えるので、スプレッドシートも頑張って欲しいです。
複数条件で運用したい方はぜひ参考にしてください。
おすすめの書籍
スプレッドシートを学びたいのであれば以下の書籍がおすすめです。
Kindle Unlimited加入者であれば無料で読むことが出来ますよ！
ポチップ
皆さんのお役に立てる記事になれば嬉しいです。
以上、ごすけでした。
