Sheet2 has dates in column A
In sheet 1 you have the start date in C4 and the end of period date in D4
(could be any other cell, but D4 for this example). Then
=SUMPRODUCT(--(Sheet2!$A:$A=$C$4),--(Sheet2!$A:$A<=$D$4),--(Sheet2!B:B))
would give you the total of values in column B as a result. The way I've
set up the formula, you can then drag it to the right on your sheet and the
final column will change from B, to C, to D, to E, etc depending on how far
across the sheet you drag or fill it.
If you put 11-Apr in C4 and 17-Apr in D4, then it would give you the totals
for that period.


On this discussion I entered the formula and adjusted the "Sheet 2" to
"OUTBOUND" to reflect the name on the sheet

=SUMPRODUCT(--(OUTBOUND!$A:$A=$C$4),--(OUTBOUND!$A:$A<=$D$4),--(OUTBOUND!B:B))

This results in a "#NUM in the cell
What have I missed?

Probably my fault, I was probably working in Excel 2007 when I built up the
formula. You'll need to specify the start and end rows for the column
references in OUTBOUND, like:
=SUMPRODUCT(--(OUTBOUND!$A$1:$A$100=Sheet1!$C$4),--(OUTBOUND!$A$1:$A$100<=Sheet1!$D$4),--(OUTBOUND!$B$1:$B$100))

You may want to increase the $A$100 and $B$100 row numbers to go well down
below where the list ends now to keep from having to revise it for a while.
In any case, that last row number needs to be the same for both the $A$###
and $B$### references.

My apologies for the headache.

"Norm" wrote:

Sheet2 has dates in column A
In sheet 1 you have the start date in C4 and the end of period date in D4
(could be any other cell, but D4 for this example). Then
=SUMPRODUCT(--(Sheet2!$A:$A=$C$4),--(Sheet2!$A:$A<=$D$4),--(Sheet2!B:B))
would give you the total of values in column B as a result. The way I've
set up the formula, you can then drag it to the right on your sheet and the
final column will change from B, to C, to D, to E, etc depending on how far
across the sheet you drag or fill it.
If you put 11-Apr in C4 and 17-Apr in D4, then it would give you the totals
for that period.


On this discussion I entered the formula and adjusted the "Sheet 2" to
"OUTBOUND" to reflect the name on the sheet

=SUMPRODUCT(--(OUTBOUND!$A:$A=$C$4),--(OUTBOUND!$A:$A<=$D$4),--(OUTBOUND!B:B))

This results in a "#NUM in the cell
What have I missed?

Success!!!
Thank you for your help on both problems
Cheers!!

"JLatham" wrote:

Probably my fault, I was probably working in Excel 2007 when I built up the
formula. You'll need to specify the start and end rows for the column
references in OUTBOUND, like:
=SUMPRODUCT(--(OUTBOUND!$A$1:$A$100=Sheet1!$C$4),--(OUTBOUND!$A$1:$A$100<=Sheet1!$D$4),--(OUTBOUND!$B$1:$B$100))

You may want to increase the $A$100 and $B$100 row numbers to go well down
below where the list ends now to keep from having to revise it for a while.
In any case, that last row number needs to be the same for both the $A$###
and $B$### references.

My apologies for the headache.

"Norm" wrote:

Sheet2 has dates in column A
In sheet 1 you have the start date in C4 and the end of period date in D4
(could be any other cell, but D4 for this example). Then
=SUMPRODUCT(--(Sheet2!$A:$A=$C$4),--(Sheet2!$A:$A<=$D$4),--(Sheet2!B:B))
would give you the total of values in column B as a result. The way I've
set up the formula, you can then drag it to the right on your sheet and the
final column will change from B, to C, to D, to E, etc depending on how far
across the sheet you drag or fill it.
If you put 11-Apr in C4 and 17-Apr in D4, then it would give you the totals
for that period.


On this discussion I entered the formula and adjusted the "Sheet 2" to
"OUTBOUND" to reflect the name on the sheet

=SUMPRODUCT(--(OUTBOUND!$A:$A=$C$4),--(OUTBOUND!$A:$A<=$D$4),--(OUTBOUND!B:B))

This results in a "#NUM in the cell
What have I missed?