Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif using dates
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif using dates
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 "txm49" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif using dates
One mo
=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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html txm49 wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif using dates
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. "Elkar" wrote: 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 "txm49" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with dates | Excel Worksheet Functions | |||
Sumif & Dates | Excel Worksheet Functions | |||
SUMIF and Dates | Excel Worksheet Functions | |||
SUMIF USING DATES | Excel Discussion (Misc queries) | |||
SUMIF between dates | Excel Worksheet Functions |