sumproduct multiple daily transactions by date and month

Try this formula in F8, then copied down:

=SUMPRODUCT(--($J$8:$J$510>E8),--($J$8:$J$510<E8+7),$K$8:$K$510)

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.

JBeaucaire, thank you.

Donkey, thanks also.


The EDATE works for the monthly totals but doesn't for the weekly. I read the info on it and it seems to work only on monthly calcs.


re: pivot tables. I have another thread asking for help on that matter (still unsolved). But I'll summarize here:

I am consolidating data from multiple worksheets. The tables in these worksheets cannot be sorted without corrupting some of their formulas. (Or... I'm sure there's a way but it would require that I post lots of questions. And for some reason I get an error message when trying to upload a 3mb zip file. ). So first issue is how to generate a pivot table using rows meeting condition x (not all rows) from multiple worksheets.

Second issue is that once I create the new summary table, then that table becomes the beginning of another set of calculations. I need to be able to access several thousand transactions in this new summary sheet, and perform various operations on the data. And... I haven't figured out how to do these (complex... such as sumifs) calcs based on pivot table cells.

Re: Weekly totals... correct, you can't use EDATE but you can use +7 as outlined by JB in earlier example.

I would still advise SUMIFS, simply replace the EDATE with +7, ie:

F8: =SUMIFS($K$8:$K$510,$J$8:$J$510,">="&$E8,$J$8:$J$510,"<"&$E8+7)
copy down