Macro for adding between dates

[rhani111]

Hi all,

Here's hopefully an easy one for the macro gurus out there...

I have several sheets that are then collated on a single "report" page
I would like to know if anyone out there has any idea on a macro or VB
module that would automatically sum totals with the criteria of star
and end dates?

The dates are added in manually each time the user wishes to update th
report. Sometimes the start and end will be on a week by week and othe
times for a whole month.

Help????

 

[duane]

This can be done with a worksheet function. Do you really want
macro?

see this application

http://www.xldynamic.com/source/xld.SUMPRODUCT.htm

 

[rhani111]

Hi Matey,

No, can't really use the sumproduct function. There are too many
variables to deal with. For a start I would need it to search for a
matching date, then sum two or three different columns depending on the
start and end dates. If sumproduct can do this, then can someone give me
an example??

I would rather have the user enter the dates and either use a button to
re-calculate or VBA entered on the sheet itself which would
automatically calculate. This way it leaves no room for the user to
make any errors.

Tar for the info though

 

[duane]

let's say the start date is in cell a1, and the end date in cell a2, and
you want to sum data in column C rows 1:100 between these dates in
column B(inclusive of start and end dates).

=sumproduct((b1:b100>=a1)*(b1+b100<=a2)*(c1:c100))

to sum data in columns c:e

=sumproduct((b1:b100>=a1)*(b1+b100<=a2)*(c1:e100))