エクセル2010のSUMIFS関数について

質問

A列に日付、C列に文字列、E列に数値があります。
仮に、日付は2013年４月だけ、文字列は”事務”　だけに条件を絞ってE列の数値を合計する場合、エクセル2000では
=SUMPRODUCT((C$1:C$1000="事務")*(TEXT(A$1:A$1000,"yyyymm")="201304")*E$1:E$1000)
でうまくいきました。

こんどエクセルが2010になったので、あたらしくできたSUMIFS関数でやってみようと思い
=SUMIFS(E:E,C:C, ”事務”,TEXT(A:A,"yyyymm"),”201304”)
としましたが「数式が正しくありません」とエラーになってしまいます。
SUMIFS関数だと列のセル範囲を指定しなくていいので使いたいのですが・・・。

F列を作業列とし、ここにA列の日付をyyyymm形式の文字列とすれば
=SUMIFS(E:E,C:C,"事務 ",F:F,"201304")
で出来ますが、ほかにいい方法はないでしょうか？

回答No.4

MarcoRossiItaly · ベストアンサー

＞SUMIFS関数では引数にセル範囲でしか指定できないのであれば、たとえば年度を問わず4月分などという指定は出来ないということでしょうか？

第 2 引数では、できません。添付図のように作業列を用意し、それを参照してください。

C2　=month(b2)
E2　=sumifs(a:a,c:c,4)

なお日時のシリアル値から年、日、時、分、秒を求めるための関数としては、YEAR、DAY、HOUR、MINUTE、SECOND 関数というものも Excel に用意されています。

2013～2015年など、年の範囲が絞れる場合は、第 3 引数に DATE を導入することで、作業列をなしにする工夫は可能かと思います。次式のどれでもお好きなものを。

E5
=sumifs(a:a,b:b,">="&date(2013,4,1),b:b,"<"&date(2013,5,1))+sumifs(a:a,b:b,">="&date(2014,4,1),b:b,"<"&date(2014,5,1))+sumifs(a:a,b:b,">="&date(2015,4,1),b:b,"<"&date(2015,5,1))
=sum(sumifs(a:a,b:b,">="&date({2013,2014,2015},4,1),b:b,"<"&date({2013,2014,2015},5,1)))
=sumproduct(sumifs(a:a,b:b,">="&date(row(2013:2015),4,1),b:b,"<"&date(row(2013:2015),5,1)))
=sumproduct(a1:a1000*(date(2013,4,1)<=b1:b1000)*(b1:b1000<date(2013,5,1)))+sumproduct(a1:a1000*(date(2014,4,1)<=b1:b1000)*(b1:b1000<date(2014,5,1)))+sumproduct(a1:a1000*(date(2015,4,1)<=b1:b1000)*(b1:b1000<date(2015,5,1)))
=sumproduct(a1:a1000,(date(2013,4,1)<=b1:b1000)*(b1:b1000<date(2013,5,1)))+sumproduct(a1:a1000,(date(2014,4,1)<=b1:b1000)*(b1:b1000<date(2014,5,1)))+sumproduct(a1:a1000,(date(2015,4,1)<=b1:b1000)*(b1:b1000<date(2015,5,1)))

＞今回は見出し行が空白だったので同じ答えを返してくれましたが見出し行の文字列対策ですね？

はい。また、各レコードにも何らかの事情で文字列などが混ざることもあるかもしれないので、そうした場合ももちろん動きが変わります。例えば空文字列「""」が途中にあると、もちろん「0」とはみなしてくれないので、「#VALUE!」のエラーとなります。

エラーを出したくない場合は「,」ですが、むしろ出したいなら「*」を使うので（表の項目の行を含めず範囲を指定）、どちらが正しいということではありません。

なお SUMIF や SUMIFS は、SUM と同様に、文字列は無視して「0」として合計してくれます。SUMPRODUCT は数値しか合計できません。

お礼

ありがとうございます。
とても勉強になりました。

回答No.3

MarcoRossiItaly

No.2 です。錯覚があったので、訂正します。

「状況によっては次式など」と書いている箇所は、次式としてください。その上の数式と比較すると、「*」を 1 つだけ「,」に書き換えた形となっています。

=SUMPRODUCT((C$1:C$1000="事務")*("2013/4/1"*1<=A$1:A$1000)*(A$1:A$1000<"2013/5/1"*1),E$1:E$1000)

お礼

>「*」を 1 つだけ「,」に書き換えた形

何度もありがとうございます。
わかりました。
今回は見出し行が空白だったので同じ答えを返してくれましたが見出し行の文字列対策ですね？

補足

しつこくすみません。SUMIFS関数では引数にセル範囲でしか指定できないのであれば、たとえば年度を問わず4月分などという指定は出来ないということでしょうか？

回答No.2

MarcoRossiItaly

SUMIFS あるいは SUMIF 関数の書式に合わない記述をしているからです。セル範囲を指定すべき引数に違うものを書けば、エラーです。

=SUMIFS(E:E,C:C,"事務",A:A,">=2013/4/1",A:A,"<2013/5/1")

SUMPRODUCT のほうの数式にしても、TEXT は不要です。

=SUMPRODUCT((C$1:C$1000="事務")*("2013/4/1"*1<=A$1:A$1000)*(A$1:A$1000<"2013/5/1"*1)*E$1:E$1000)

状況によっては次式など
=SUMPRODUCT((C$1:C$1000="事務")*1,("2013/4/1"*1<=A$1:A$1000)*1,(A$1:A$1000<"2013/5/1"*1)*1,E$1:E$1000)

なお「2013/5/1」というふうに翌月 1 日を基準にすれば、大の月／小の月の違いにより人為的なミスをする可能性を排除できます。

日付・日時のデータの場合は、殆どの場面で、数値のほうが利用しやすいと思います。今後なるべく、セルの書式や TEXT 関数で文字列にはしないでおくことをお勧めします。日付と文字列を「&」で結合する場合はどのみち文字列となるので、気にしなくていいですが。

お礼

さっそくありがとうございました。
1行目をデータに変えてもエラーでした。
ご教示いただいた、
=SUMIFS(E:E,C:C,"事務",A:A,">=2013/4/1",A:A,"<=2013/4/30")
では大丈夫でした。
SUMIFSでは,TEXT(A:A,"yyyymm"),のような範囲指定は受け付けてくれないようです。

補足

すみません、No1さまへのお礼を間違えて書いてしまいました。

> セル範囲を指定すべき引数に違うものを書けば、エラーです。

わかりました。
有難うございます。

回答No.1

tom04

こんにちは！
通常1行目は項目行として使用すると思いますので、
おそらくお示しのSUMPRODUCT関数の場合1行目から範囲指定してあるので
項目行が文字列の場合、エラーになるのではないかと思います。
（項目行がなければ問題なく表示されると思います）

それはさておいてSUMIFS関数でやる場合は
A列はシリアル値が入っていると思いますので、
表示したいセルに
=SUMIFS(E:E,C:C,"事務",A:A,">=2013/4/1",A:A,"<=2013/4/30")

といった感じではどうでしょうか？
この場合、1行目に項目の文字列があっても問題なく表示されると思います。m(_ _)m

お礼

さっそくありがとうございました。
1行目をデータに変えてもエラーでした。
ご教示いただいた、
=SUMIFS(E:E,C:C,"事務",A:A,">=2013/4/1",A:A,"<=2013/4/30")
では大丈夫でした。
SUMIFSでは,TEXT(A:A,"yyyymm"),のような範囲指定は受け付けてくれないようです。