Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the FormulaArray property of the Range class
Trying to use conditional sum for a range of dates. this used to work in
Excel 95 doesn't seem to work here. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the FormulaArray property of the Range class
More information------------
If the selected cells do fall within a date range, corresponding cells, need to be summed. I am using this to sum items that are shipped in a given month. "jim kozak" wrote: Trying to use conditional sum for a range of dates. this used to work in Excel 95 doesn't seem to work here. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the FormulaArray property of the Range class
Have you tried
=SUMPRODUCT(--(rng=--"2005-03-01"),--(rng<=--"2005-03-31")) which counts all items in March -- HTH RP (remove nothere from the email address if mailing direct) "jimkozak" wrote in message ... More information------------ If the selected cells do fall within a date range, corresponding cells, need to be summed. I am using this to sum items that are shipped in a given month. "jim kozak" wrote: Trying to use conditional sum for a range of dates. this used to work in Excel 95 doesn't seem to work here. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the FormulaArray property of the Range class
If I wanted to sum a specific month here is how I might go about it
=SUM(IF(MONTH(A2:A60)=1, B2:B60)) When you enter this it is an array formula so you need to hit Shift + Ctrl + Enter, instead of just enter. You will know if it is an array formula because it will end up with curly braces around it{}. Or better yet create a pivot table and group on the dates field to aggregate by months. That is actually a heck of a lot easier if you want to aggregate based on years, months, quarters... and / or by customer, region, store... Let me know if you want help giving that a try. It is really easy... HTH "jimkozak" wrote: More information------------ If the selected cells do fall within a date range, corresponding cells, need to be summed. I am using this to sum items that are shipped in a given month. "jim kozak" wrote: Trying to use conditional sum for a range of dates. this used to work in Excel 95 doesn't seem to work here. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the FormulaArray property of the Range class
sounds interesting. right now i was able to get around the problem by
actualling entering the upper and lower limits in the column 4/1/2005 and 4/30/2005, then using conditional sum, then deleting the limits. It worked but i never should have had to enter the limits. What I have is a series of quotes in an excell spreadsheet, ordered by date. When these quotes are approved, I add the date in the next column, and when they are shipped, I add that date. All I am trying to do is add the shipped numbers. I know that I could sort based on shipping date but I like keeping them in quote order. "Jim Thomlinson" wrote: If I wanted to sum a specific month here is how I might go about it =SUM(IF(MONTH(A2:A60)=1, B2:B60)) When you enter this it is an array formula so you need to hit Shift + Ctrl + Enter, instead of just enter. You will know if it is an array formula because it will end up with curly braces around it{}. Or better yet create a pivot table and group on the dates field to aggregate by months. That is actually a heck of a lot easier if you want to aggregate based on years, months, quarters... and / or by customer, region, store... Let me know if you want help giving that a try. It is really easy... HTH "jimkozak" wrote: More information------------ If the selected cells do fall within a date range, corresponding cells, need to be summed. I am using this to sum items that are shipped in a given month. "jim kozak" wrote: Trying to use conditional sum for a range of dates. this used to work in Excel 95 doesn't seem to work here. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to set the FormulaArrary property of the range class | Excel Worksheet Functions | |||
unable to set the formula array property of the range class | Excel Worksheet Functions | |||
Unable to set the Locked property of the range class | Excel Programming | |||
unable to set formulaarray of range class ERROR when using Conditional Sum | Excel Programming | |||
Unable to set the Locked Property of the Range Class | Excel Programming |