sumif using dates

[Guest]

Column A has mixed dates "m/d/yyyy". I want to sum the associated data in
Column B for each m/y. Sample layout below:

Column A Column B
8/31/2007 5
8/31/2007 15
1/30/2007 10
1/30/2007 20
6/28/2007 30
6/28/2007 10

I want to pull this data into a new file that will have results for Jan
2007, Jun 2007, and Aug 2007. I need the right sum formula, not a macro.

 

[Guest]

Try this:

=SUMPRODUCT(--(MONTH(A1:A100)=1),--(YEAR(A1:A100)=2007),B1:B100)

This would give you the sum for all dates in January of 2007. To get June,
just change the Month portion to =6, and August would be =8.

HTH,
Elkar

 

[Dave Peterson]

One more:

=sumproduct(--(text(a1:a100,"yyyymm")="200701"),b1:b100)

Your function will probably look more like:

=SUMPRODUCT(--(TEXT([book1.xls]Sheet1!A1:A100,"yyyymm")="200701"),
[book1.xls]Sheet1!B1:B100)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Guest]

Tried this: =SUMPRODUCT(--(MONTH('M:\CPE\Raw Data\[SE - MTD
CPE.xls]DATA'!$A$1:$A$20000)=1),--(YEAR('M:\CPE\Raw Data\[SE - MTD
CPE.xls]DATA'!$A$1:$A$20000)=2007),('M:\CPE\Raw Data\[SE - MTD
CPE.xls]DATA'!$I$1:$I$20000))



and got a window that read "Excel cannot complete this task with available
resources. Choose less data or close other applications"

so I guess we'll never know.