Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Sum and DATEVALUE function
I have been trying to create a reusable formula that calculates monthly sums
and can easily adjust values for what-ifs. I used the Conditional Sum Wizard add-in to create the following formula: =SUM(IF($A$4:$A$2101=DATEVALUE("10/1/04"),IF($A$4:$A$2101<=DATEVALUE("10/31/04"),$E$4:$E$2101,0),0)) A comparable formula would be assigned to other cells for additional monthly ranges; however, it is impractical to keep adding different ranges, as each sheet contains about 36 months that are sequential but begin and end at varying points. The DATEVALUE function strikes me as one of the stupidest in Excel because of the inability to input cell ranges, forcing you to key in specific dates every time. I was hoping someone out there knows a way around this limitation. I'd appreciate any help. |
#2
|
|||
|
|||
Are you looking for an easier way to revise your date range?
You could refer the formula to specific cells for your start and end dates: =SUM(IF($A$4:$A$2101=A3,IF($A$4:$A$2101<=B3,$E$4: $E$2101,0),0)) Also, since this is an array formula, you might wish to revise it to a non-array alternative: =SUMPRODUCT((A4:A2101=A3)*(A4:A2101<=B3)*E4:E2101 ) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Vlad" wrote in message ... I have been trying to create a reusable formula that calculates monthly sums and can easily adjust values for what-ifs. I used the Conditional Sum Wizard add-in to create the following formula: =SUM(IF($A$4:$A$2101=DATEVALUE("10/1/04"),IF($A$4:$A$2101<=DATEVALUE("10/31 /04"),$E$4:$E$2101,0),0)) A comparable formula would be assigned to other cells for additional monthly ranges; however, it is impractical to keep adding different ranges, as each sheet contains about 36 months that are sequential but begin and end at varying points. The DATEVALUE function strikes me as one of the stupidest in Excel because of the inability to input cell ranges, forcing you to key in specific dates every time. I was hoping someone out there knows a way around this limitation. I'd appreciate any help. |
#3
|
|||
|
|||
On Tue, 14 Jun 2005 08:05:02 -0700, "Vlad"
wrote: I have been trying to create a reusable formula that calculates monthly sums and can easily adjust values for what-ifs. I used the Conditional Sum Wizard add-in to create the following formula: =SUM(IF($A$4:$A$2101=DATEVALUE("10/1/04"),IF($A$4:$A$2101<=DATEVALUE("10/31/04"),$E$4:$E$2101,0),0)) A comparable formula would be assigned to other cells for additional monthly ranges; however, it is impractical to keep adding different ranges, as each sheet contains about 36 months that are sequential but begin and end at varying points. The DATEVALUE function strikes me as one of the stupidest in Excel because of the inability to input cell ranges, forcing you to key in specific dates every time. I was hoping someone out there knows a way around this limitation. I'd appreciate any help. Are you wedded to the DATEVALUE function? Why not put your start date and end date in two cells and use the following formula: =SUMIF($A$4:$A$2101,"="&StartDate,$E$4:$E$2101) - SUMIF($A$4:$A$2101,""&EndDate,$E$4:$E$2101) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|