20日締めの表示がずれる

質問

いつもお世話になります。
WIN7 EXCELLL2010 です。

添付図で説明さていただくと、
A2 A3 は20日の締めで翌月の入金の設定したいのです。
ところが　「I6」に「2,500」が1列ずれて表示されます。
これを　　「J6」に「2,500」に修正したい。

どこか私のやり方に問題があると思いますが御指導をいただけませんでしょうか。

参考に
I1 =DATE($D$1-1,12,1)
J1 =DATE($D$1,1,1)
I6 =SUMPRODUCT(($A$2:$A$1000>=DATE(YEAR($I$1),MONTH($I$1)-1,VLOOKUP($E7,$E$3:$G$7,2,0)))*($A$2:$A$1000<=DATE(YEAR($I$1),MONTH($I$1),VLOOKUP($E7,$E$3:$G$7,3,0)))*($B$2:$B$1000=$E7)*($C$2:$C$1000))

回答No.1

bunjii · ベストアンサー

>どこか私のやり方に問題があると思いますが御指導をいただけませんでしょうか。
>I1 =DATE($D$1-1,12,1)
>J1 =DATE($D$1,1,1)
D1セルには2014と入力されていますか？
比較の日付を誤っているためです。
毎月20日で締めて翌月X日に入金と云う条件であればI1は入金月の1日と考えれば次のようにしなければなりません。
J6=SUMPRODUCT(($A$2:$A$1000>=DATE(YEAR($I$1),MONTH($I$1)-1,VLOOKUP($E7,$E$3:$G$7,2,0)))*($A$2:$A$1000<=DATE(YEAR($I$1),MONTH($I$1),VLOOKUP($E7,$E$3:$G$7,3,0)))*($B$2:$B$1000=$E7)*($C$2:$C$1000))
但し、この式にはタイプミスと思われる誤りがありますので他のセルへコピーしても期待通りに動作しません。

明らかに毎月21日から翌月20日までの集計なので上記の数式は無駄な小細工になっています。
I1、J1セルは単純に各月の入金予定日で良いでしょう。
F、G列も不要な状態で数式を簡略化できます。
I3=SUMPRODUCT(($B$2:$B$1000=$E3)*1,($A$2:$A$1000>EOMONTH(I$1,-3)+20)*1,($A$2:$A$1000<EOMONTH(I$1,-2)+20)*1,($B$2:$B$1000=$E3)*$C$2:$C$1000)
I3セルを右へ必要数と下へ必要数コピーすれば添付画像のようになります。
Excel 2013で検証しましたがExcel 2007/2010でも同様な結果が得られるはずです。

お礼

早速の御指導をありがとうございます。
上手くてきました。
その上に簡略てきてうれしい限りです。

D1セルには2014と入力されていますか？
間違いなく入力しています。