SUMIFSに日付を使いたいのですが…

質問

どなたか助けて下さい。

現在のデータとして複数のシートがあり、年月で纏める為にSUMIFSを使おうと考えております。
1枚目のシートには日付が打ち込んであり、年月日まで入っています。（yyyy/m/d）

Sheet１
A　　　　　　　　　　　　　　　　　B
2008/11/29（yyyy/m/d）　　30
2008/12/29（yyyy/m/d）　　50
2008/12/31（yyyy/m/d）　　100

2枚目のシートには年月が入力されてあり、「200812」と数字が入っております。

A　　　　　　　B
200812　　　？
最終的には2枚目のBに12月の合計を纏めたいので、1枚目の日付を変更したいのですが
書式設定の表示形式のユーザー定義で「yyyymm」にすると表示は「200812」
ですが、当り前の事ながら、データとしてはそのように反応してくれません。
（合計値として150となるようにしたい）

そこで、これをどのように結び付けるのか、また別の方法があればと思い
どなたか教えて頂ければ幸甚です。

回答No.4

kokoko555 · ベストアンサー

No1で　回答したものです
他の方が回答されたように　Sheet２を日付データとして入力し直が出来るなら　月を抜き出す必要はありませんのでそうした場合の数式を
=SUMPRODUCT((YEAR(Sheet1!$A$1:$A$3)=YEAR(sheet2!A1))*(MONTH(Sheet1!$A$1:$A$3)=MONTH(shet2!A2))*(Sheet1!$B$1:$B$3))

お礼

どうもありがとうございます。
年と月を切り分けてSUMIFをやってみましたら反応しました。
SUMPRODUCTを使った事が無いのでこれを機に勉強します。

今後も宜しくお願い申し上げます。

回答No.5

Nouble

失礼しました
確かにSUMIFS関数バグってるっぽいですね

=SUMIFS(Sheet1!$B:$B,Sheet1!$A:$A,">=39783",Sheet1!$A:$A,"<39814")
としたらちゃんと150とでるのに
=SUMIFS(Sheet1!$B:$B,Sheet1!$A:$A,">=date(left(A2、4)、right(A2，2)，1)",Sheet1!$A:$A,"<date(left(A2、4)、right(A2，2)+1，1)")
としたら
">=date(left(A2、4)、right(A2，2)，1)"
を">=39783"と
"<date(left(A2、4)、right(A2，2)+1，1)"
を"<39814"と
評価せずに0と出しますね

だからといって
評価対象部分を切り出して先に計算させておく為に
B5に
=DATE(LEFT(A2,4),RIGHT(A2,2),1)
B6に
=DATE(LEFT(A2,4),RIGHT(A2,2)+1,1)
と入れておいて
=SUMIFS(Sheet1!$B:$B,Sheet1!$A:$A,">=B5",Sheet1!$A:$A,"<B6")
としても結果は0
変わらない始末

こんな事もしてみたのですが
=SUMIFS(Sheet1!B3:B5,text(Sheet1!A3:A5,"yyyymmdd"),=CONCATENATE(A2,"??"))
=SUMIFS(Sheet1!B3:B5,Sheet1!A3:A5,CONCATENATE("=",INDEX(Sheet1!A3:A5*(TEXT(Sheet1!A3:A5,"yyyy")=LEFT(シート２!A2,4)),0)),Sheet1!A3:A5,CONCATENATE("=",INDEX((TEXT(Sheet1!A3:A5,"mm")=RIGHT(シート２!A2,2))*Sheet1!A3:A5,0)))
駄目でした

唯一叶ったのが
A列のデータにC列で
=text(A3,"YYYYMMDD")
としておいて下へフィル
この列を対象に
=SUMIFS(Sheet1!B3:B5,Sheet1!C3:C5,CONCATENATE("=",A2,"??"))
とした時と
同意味の
=SUMIFS(Sheet1!B3:B5,Sheet1!C3:C5,"="&A2&"??")
のみでした

此では確かにあきれ果てますよね

貴殿の真意を汲めず軽率な発言をして申し訳ありませんでした
今後のMSの対応待ちではないでしょうか
早期対応を期待します

因みに計算するだけなら
=SUMPRODUCT((TEXT(Sheet1!$A:$A,"yyyymm")=TEXT(A2,"@"))*Sheet1!$B:$B)
とか
=SUMPRODUCT((VALUE(TEXT(Sheet1!$A:$A,"yyyymm"))=A2)*Sheet1!$B:$B)
とかで出来ますね

お礼

まずは私の拙い質問に対しいくつもの検証含めこれほどの返信を頂き大変恐縮・幸甚です。

見た事の無い関数もあり（＠_＠）ひとつひとつ勉強しようと思います。
今後とも何卒宜しくお願い申し上げます。

回答No.3

Nouble

要はデータ形式不一致な点を危惧されているのだと読みました

通常こういった集計ではピボットテーブルを使う方が
慣れてしまえば遥かに楽で早いので
こちらをお勧めするのですが
それでは脳がありませんので

敢えてsumif系の関数を使う方向でお話しします

まず両関数の解説が掲載されたページを挙げておきます

sumifs
http://www.becoolusers.com/excel/sumifs.html

sumif
http://www3.tokai.or.jp/excel/kansu/sumif.htm

では本題ですが
上記サイトを拝見頂けるとお分かりの通り
検索条件が今回のように１条件のみの場合
両関数に然して差が生まれるものではないように思えます

そこで敢えて条件を「年と月の２つが共に合致する」の２つに分けて
お話しさせて頂こうと思います

最初の話に戻りますが
今回の問題点は
シート１では日付
詰まり数値としてデータか記されているのに対し
シート２では文字列として指定されています
問題はこの一点に尽きると言っていいでしょう

奈良はデータ形式を合わせて後に比較すれば
良いことになると思います

先に述べた通りここでは年号と何月かを別に取得します

まずシート１からですが
年号は
=year(A$:A$)

次に何月かは
=month(A$:A$)

で、各々取得できます

シート２では
年号は
=left(A1,4)

次に何月かは
=right(A1,2)

で、各々取得できます

このままでは確かに数値と文字形式で
比較にならないと一見おもいがちですが
これくらいはエクセルが察してくれて許容してくれるので
このまま比較できます

なのでこのシート１とシート２から取り出した各々のデータが一致したものを
関数の機能に任せて集計すればいいのだと思います

この事を先に挙げたページの記載内容に習い当てはめてみます

=sumifs(Sheet1!C$:C$,Sheet1!A$:A$,"year(Sheet1!A$:A$)=left(A1,4)",Sheet1!A$:A$,"month(Sheet1!A$:A$)=right(A1,2)")

もしくは
=sumifs(Sheet1!C$:C$,Sheet1!A$:A$,"and(>=date(left(A1,4),right(A1,2),1),<date(left(A1,4),right(A1,2)+1,1)")

て、とこでしょうか

"and(>=date(left(A1,4),right(A1,2),1),<date(left(A1,4),right(A1,2)+1,1)"
は、後半を少し変えて
"and(>=date(left(A1,4),right(A1,2),1),<=eomonth(date(left(A1,4),right(A1,2),1))"

としてもいいかもしれません

と、まあ偉そうなことを書きましたが
実際今携帯で書いているので確認が取れてない上に
この関数使ったこと無いんですよね

的外れだったら済みません

お役にたてたなら幸いです

ｐｓ
どちらも駄目に見えてきた
汗

回答No.2

hige_082

Sheet2の
Ａ1を
2008/12/1(yyyymm)
として
Ｂ１に
=SUMPRODUCT((MONTH(Sheet1!A1:A3)=MONTH(A1))*Sheet1!B1:B3)
とすれば
Sheet1の日付の変更は必要ありません

お礼

ありがとうございます。
これは月だけでSUMするようですが、過去3年分程あり
このSUMPRODUCTに「年」と「月」を持たせるようにするには
どのようにしたら宜しいのでしょうか？

回答No.1

kokoko555

SUMIFSは使い慣れていないので　別の方法で
１枚目の日付を変更するのではなく、２枚目の日付を工夫したほうが良いと思います（月を抜き出す）

２枚目のＢ1 に　=RIGHT(A1,2)*1
Ｃ1 に合計を求める式を
　=SUMPRODUCT((MONTH(Sheet1!A1:A3)=Sheet2!B1)*(Sheet1!B1:B3))

お礼

早速のお返事ありがとうございます。
まずは試してみます。
ただ月を抜き出す方法大変参考になりました。

今後も宜しくお願い申し上げます。

補足

質問した者ですm(_ _)m

ありがとうございます。
これは月だけでSUMするようですが、過去3年分程あり
このSUMPRODUCTに「年」と「月」を持たせるようにするには
どのようにしたら宜しいのでしょうか？