Sum between two dates

[Gusher]

In cell A1 I have a date, in cell A2 I have another later date. In column B I have dates and in column C and D I have numbers. I need a function to return the sum of the numbers in cols C and D between the dates specified in cells A1 and A2.

Many thanks

 

[Jonmo1]

You're welcome.

It's partially just a way to pass time.
But mostly I find helping other people is a great way to learn, and keep your skills sharp.
I'm usually answering about stuff I already know, but I do come accross issues I don't immediately know the solution to, and that pushes me to figure it out. Thus expanding my own knowledge.

 

[Gusher]

You're welcome.

It's partially just a way to pass time.
But mostly I find helping other people is a great way to learn, and keep your skills sharp.
I'm usually answering about stuff I already know, but I do come accross issues I don't immediately know the solution to, and that pushes me to figure it out. Thus expanding my own knowledge.

Hello Jonmo1,


I have another question please. The formula you helped me with has to read data from 40 different exel sheet so the formula is different 40 times which is tedious to edit. The data cells are in identical positions on all the sheets. All the sheets are named. So I wondered if it is possible in a column to create a list of sheet names, for example A1 to A40. Then in column B1 to B40 I have the formula you gave me but in each formula points to the adjacent cell in col A to get the sheet name for the formula. Many Thanks , Mark

 

[Jonmo1]

You can use INDIRECT for that

=SUMPRODUCT((INDIRECT("'"&H1&"'!B1:B100")>=A1)*(INDIRECT("'"&H1&"'!B1:B100")<=A2)*INDIRECT("'"&H1&"'!c1:c100"))

Where H1 is a cell containing the sheetname.

 

[Gusher]

You can use INDIRECT for that

=SUMPRODUCT((INDIRECT("'"&H1&"'!B1:B100")>=A1)*(INDIRECT("'"&H1&"'!B1:B100")<=A2)*INDIRECT("'"&H1&"'!c1:c100"))

Where H1 is a cell containing the sheetname.

Thanks, and any thoughts on how I can create a list of sheet names in a column?

 

[Jonmo1]

That's going to require VBA, and there are lots of threads on that already.
Try searching the forum for create list of sheet names.