Conditional Sum and DATEVALUE function

[Guest]

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.

 

[RagDyeR]

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!
==============================================


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.

 

[Ron Rosenfeld]

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