sumproduct multiple daily transactions by date and month

If as you say the file is big avoid using Sumproduct / Arrays in any volume as your file will grind to a halt.

Given you're using 2007 you should wherever possible make use of SUMIFS function over Sumproduct (though not backwards compatible with earlier versions) -- this is significantly more efficient, eg:

C8:
=SUMIFS($K$8:$K$510,$J$8:$J$510,">="&$B8,$J$8:$J$510,"<"&EDATE($B8,1))
copy down
(uses EDATE which is available by default in XL2007)

I'm sure you can establish the formulae for weekly totals...

Alternatively why not combine the results and use one Pivot Table ?
(see attached - 07 format)

EDIT: I see you say you can't use PT's - care to elaborate as to why ? I'm not disagreeing but always useful to outline why you have x restrictions in place as these may impact other potential solutions.