Need help with using date as criteria for sum function

[LegaLega]

Hello all,

On a worksheet, I have a column which contains a date and a colum
which contains a corresponding amount of records. What I need to do i
get the sum of all records for a given date (i.e. all records for May
all records for June, etc.) I've attempted to accomplish this usin
=(SUMPRODUCT(('Data Sheet'!$A:$A>=VALUE("01 May 2004))*('Dat
Sheet'!$A:$A<=VALUE("31 May 2004))*'Data Sheet'!$F:$F)) - where the
column contains dates within date-formatted cells, and the F colum
contains total records. Can someone please help me

 

[LegaLega]

Sorry, I forgot to iterate - there is also additional criteria, henc
the reason I used the sumproduct function. There are other column
which specify specific categories via text. Thank you

 

[Don Guillett]

should work if you use another " after May 2004
May 2004"

 

[RagDyeR]

Besides adding the parenthesis, Sumproduct doesn't work with total column
references (A:A).
You could use A1:A65000
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

should work if you use another " after May 2004
May 2004"

 

[Don Guillett]

Oops. Didn't notice that part

--
Don Guillett
SalesAid Software
(e-mail address removed)

Besides adding the parenthesis, Sumproduct doesn't work with total column
references (A:A).
You could use A1:A65000
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

should work if you use another " after May 2004
May 2004"

 

[LegaLega]

I've tried using a numbered column range (i.e. A1:A10000) in place o
the entire column range (i.e. $A:$A) by using the following code
*=(SUMPRODUCT(('Data Sheet'!A2:A10000>=VALUE("5/1/2004"))*('Dat
Sheet'!A1:A10000<=VALUE("5/31/2004"))*'Data Sheet'!F2:F10000))*

I still cannot get the code to operate correctly though. Now I get a
*#N/A* error..

 

[Don Guillett]

You need to match your ranges. Use either 1 or 2 throughout.
Sheet'!A1:A10000 A2:A10000 F2:F10000

 

[LegaLega]

Oh my God... I'm an idiot! Thank you

 

[LegaLega]

do you know how I could make the range dynamic... so that I don't hav
to eventually rewrite the codes once I fill up the rows

 

[Don Guillett]

Make a couple of dynamic named ranges
insert>name>define>nameit daterng>in the refers to box
=offset($a$2,0,0,counta($a:$a),1)
then use that instead of a2:a10000

sumrng
=offset($f$2,0,0,counta($a:$a),1)

I would then put my start/stop dates in cells and refer to them. Again, no
formula change.
=sumproduc((daterng>b1)*(daterng<b2)*sumrng)

 

[Dana DeLouis]

A different idea might be to make a Pivot table of your data. On your Pivot
table, right click in your Date column, and select "Group and Show Detail."
Then select Group, "By Month" You may want a helper column if your dates
span multiple years.