Conditional Sum and DATEVALUE function

[Vlad]

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


"Vlad" <[email protected]> wrote in message
news:[email protected]...
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]

On Tue, 14 Jun 2005 08:05:02 -0700, "Vlad" <[email protected]>
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