sumproduct multiple daily transactions by date and month

[jrtaylor]

Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.

PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.

Thanks!

[JBeaucaire]

Try this formula in F8, then copied down:

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

[DonkeyOte]

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.

[jrtaylor]

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.

[DonkeyOte]

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

[jrtaylor]

JB and DonkeyOte

Thanks so much for your help. I used the sumproduct for the weekly totals, which works. As this is the only instance in the spreadsheet where I'll use the function, it isn't causing problems.

[DonkeyOte]

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

[jrtaylor]

Yes, that worked. Thanks again.