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.