Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Dates in a Formula
Hi,
I need to change the reference (=B474) in this formula to something that will select all entries in the range A12:A399 that fall within the month of SEP 06 then sum the numbers found in Column E. The cells in range A12:A39 are formatted as dates. I don't know how to enter this. Please help. =SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399)) Thanks -- Jerry |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Dates in a Formula
Try this im assuming that there is no data for 2007
9 is for the month of Sept =SUMPRODUCT((MONTH($A$12:$A$399)=9)*($B$12:$B$399= "PI")*($E$12:$E$399)) "Jerry Rogers" wrote: Hi, I need to change the reference (=B474) in this formula to something that will select all entries in the range A12:A399 that fall within the month of SEP 06 then sum the numbers found in Column E. The cells in range A12:A39 are formatted as dates. I don't know how to enter this. Please help. =SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399)) Thanks -- Jerry |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Dates in a Formula
One way
Try: =SUMPRODUCT(($B$12:$B$399="PI")*(TEXT($A$12:$A$399 ,"mmmyy")="Sep06")*($E$12:$E$399)) Above assumes real dates within A12:A399 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jerry Rogers" wrote: Hi, I need to change the reference (=B474) in this formula to something that will select all entries in the range A12:A399 that fall within the month of SEP 06 then sum the numbers found in Column E. The cells in range A12:A39 are formatted as dates. I don't know how to enter this. Please help. =SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399)) Thanks -- Jerry |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Dates in a Formula
I like not to hard code formula so this example lets you choose in cell A1
a number from 1 - 12 to represent the month and cell A2 lets you choose the year Try it out let me know if this is what your looking for =SUMPRODUCT((MONTH($A$12:$A$399)=A1)*(YEAR($A$12:$ A$399)=A2)*($B$12:$B$399="PI")*($E$12:$E$399)) "Jerry Rogers" wrote: Hi, I need to change the reference (=B474) in this formula to something that will select all entries in the range A12:A399 that fall within the month of SEP 06 then sum the numbers found in Column E. The cells in range A12:A39 are formatted as dates. I don't know how to enter this. Please help. =SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399)) Thanks -- Jerry |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Dates in a Formula
Hi Mike,
Thanks for the response. I plugged in the formula you provided and received a #VALUE message. I do have data with dates in 2007. Is the 2007 data the cause of the error? How can I differentiate between years? -- Jerry "Mike" wrote: Try this im assuming that there is no data for 2007 9 is for the month of Sept =SUMPRODUCT((MONTH($A$12:$A$399)=9)*($B$12:$B$399= "PI")*($E$12:$E$399)) "Jerry Rogers" wrote: Hi, I need to change the reference (=B474) in this formula to something that will select all entries in the range A12:A399 that fall within the month of SEP 06 then sum the numbers found in Column E. The cells in range A12:A39 are formatted as dates. I don't know how to enter this. Please help. =SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399)) Thanks -- Jerry |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Dates in a Formula
I like not to hard code formula so this example lets you choose in cell A1
a number from 1 - 12 to represent the month and cell A2 lets you choose the year Try it out let me know if this is what your looking for =SUMPRODUCT((MONTH($A$12:$A$399)=A1)*(YEAR($A$12:$ A$399)=A2)*($B$12:$B$399="PI")*($E$12:$E$399)) "Jerry Rogers" wrote: Hi Mike, Thanks for the response. I plugged in the formula you provided and received a #VALUE message. I do have data with dates in 2007. Is the 2007 data the cause of the error? How can I differentiate between years? -- Jerry "Mike" wrote: Try this im assuming that there is no data for 2007 9 is for the month of Sept =SUMPRODUCT((MONTH($A$12:$A$399)=9)*($B$12:$B$399= "PI")*($E$12:$E$399)) "Jerry Rogers" wrote: Hi, I need to change the reference (=B474) in this formula to something that will select all entries in the range A12:A399 that fall within the month of SEP 06 then sum the numbers found in Column E. The cells in range A12:A39 are formatted as dates. I don't know how to enter this. Please help. =SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399)) Thanks -- Jerry |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Dates in a Formula
Thanks Mike,
I plugged in Max's suggestion just before this arrived and it worked fine. I haven't tried your suggestion so I'm not sure if it works or not. -- Jerry "Mike" wrote: I like not to hard code formula so this example lets you choose in cell A1 a number from 1 - 12 to represent the month and cell A2 lets you choose the year Try it out let me know if this is what your looking for =SUMPRODUCT((MONTH($A$12:$A$399)=A1)*(YEAR($A$12:$ A$399)=A2)*($B$12:$B$399="PI")*($E$12:$E$399)) "Jerry Rogers" wrote: Hi, I need to change the reference (=B474) in this formula to something that will select all entries in the range A12:A399 that fall within the month of SEP 06 then sum the numbers found in Column E. The cells in range A12:A39 are formatted as dates. I don't know how to enter this. Please help. =SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399)) Thanks -- Jerry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula With Dates | Excel Worksheet Functions | |||
Formula for dates | Excel Discussion (Misc queries) | |||
how do I use if formula using dates? | Excel Worksheet Functions | |||
formula for dates | Excel Discussion (Misc queries) | |||
dates formula | Excel Worksheet Functions |